Which query is more efficient? (Postgresql)

Question

I have a table that will have many records (Millions). When one of the ".._ups" values is set to 0, I need to check if all the other "ups" types for that record are also 0, and delete it. This occurs because a user can cancel their "ups" of a particular type, but not of another type. If they cancel every type of "up", I want to delete the record.

The time_unit field is a time unit that changes every 5 minutes. So each vote records what time_unit it belongs to.

Is it more efficient to only search for (delete) votes with that time unit, or search for (delete) all of the votes in the (potentially huge) table? I plan on indexing time_unit. It's hard for me to test this because I don't have the records yet.

Query 1

DELETE FROM ups 
WHERE time_unit = $tuid AND big_ups = 0 AND sol_ups = 0 AND blue_ups = 0;

or

Query 2

DELETE FROM ups 
WHERE big_ups = 0 AND sol_ups = 0 AND blue_ups = 0;

The key (no pun intended) to efficient searches is using indexes - with the right indexes the number of rows in a table is irrelevant (this is how Google can know the contents of hundreds of billions of web-pages and still get you results in under 1 second). What indexes do you have set-up on the table? — Dai
– Dai, Commented Nov 7, 2019 at 1:29

The Impaler · Accepted Answer · 2019-11-07 01:35:41Z

1

Your search condition is clear:

Delete all rows where all three ups are zero.

You explain the time frame is not important when it comes to deleting the rows. You just want to get rid of rows that match the condition above.

Then, the second query is the best one if you happen to have the right index. The index you need is:

create index ix1 on ups (big_ups, sol_ups, blue_ups);

With that index the deletion should be quite fast since PostgreSQL performs logical deletion on the heap, not a physical one.

answered Nov 7, 2019 at 1:35

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Douglas Over a year ago

Awesome!! I wasn't sure if this would be faster. I will use this solution. Thanks.

sbrbot · Accepted Answer · 2019-11-07 01:36:09Z

1

With first query (using time_unit=$tuid when it is indexed) database will go directly to that record (only) just and check if other columns are zero for deletion. In second query database must pass through all records (ful table scan) and look if they there are zeroes in your columns.

answered Nov 7, 2019 at 1:36

sbrbot

6,5157 gold badges49 silver badges83 bronze badges

1 Comment

Douglas Over a year ago

Okay, thanks. That's what I was thinking. Wanted to be sure.

Collectives™ on Stack Overflow

Which query is more efficient? (Postgresql)

2 Answers 2

1 Comment

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Related