Which sql query is faster?

Question

I'm working with postgresql database and want to improve query (First one). I rewritten it to Second one. But I read article, which says that "NOT IN" is very slow construction. I want you to tell which of two is faster and/or suggest better solution.

First query

UPDATE reseller_product d SET status=3 FROM (
    SELECT reseller_product.sku FROM reseller_product
    LEFT OUTER JOIN main_table ON main_table.sku=reseller_product.sku
    WHERE main_table.sku IS NULL AND reseller_product.reseller_id='||resID||'
)as r
WHERE d.sku=r.sku and d.distributor_id='||distrID||' and d.reseller_id='||resID||'

Second query

UPDATE reseller_product SET status=3
WHERE distributor_id='||distrID||' AND reseller_id='||resID||' 
AND sku NOT IN (SELECT sku FROM main_table);

EDIT

Sorry, didn't notice error with name "d" in second query

The answer depends on the NULLability of .sku and its uniqueness and cardinality. BTW: there is a third version of this query using `NOT EXISTS()` ALSO: the correlation name `d` is not defined in the second query. So the query will fail, so it will be the fastest. — wildplasser, Dec 25 '14 at 11:36
You can use the command "EXPLAIN" to detail the execution plan's of the queries - this may help you out: http://www.postgresql.org/docs/9.2/static/sql-explain.html — n34_panda, Dec 25 '14 at 11:47
@wildplasser the correlation name d is not defined in the second query. So the query will fail, so it will be the fastest. This is really true and funny. :) — Vipul Hadiya, Dec 25 '14 at 11:48
Even funnier: it will run in constant time, independent of the structure, size and distribution of the data! — wildplasser, Dec 25 '14 at 11:51

score 0 · Answer 1 · answered Dec 25 '14 at 11:45

Rewriting the query might help somewhat, but I would first check whether main_table and reseller_product both have indexes on sku. Searching (and joining) non-indexed field has O(n) complexity, whereas for an indexed field it will be O(log(n)) or constant, depending on the index implementation.

score -1 · Accepted Answer · answered Dec 25 '14 at 11:39

-1

Third version:

UPDATE reseller_product u
SET status = 3
WHERE u.distributor_id = '||distrID||'
  AND u.reseller_id = '||resID||' 
  AND NOT EXISTS (
    SELECT * FROM main_table m
    WHERE m.sku = u.sku
    );

answered Dec 25 '14 at 11:39

wildplasser

43,142
8
66
109

Which sql query is faster?

2 Answers2