Обсуждение: different queries and their efficiencies

Hi,

I'm curious as to what the general opinion is on these different
queries and their relative merits, especially in terms of speed:

-----
select count(*) from zstore_temp where product_code not in (select
product_code from zstore)

                                   QUERY PLAN
--------------------------------------------------------------------------------
 Aggregate  (cost=17200040774.42..17200040774.42 rows=1 width=0)
   ->  Seq Scan on zstore_temp  (cost=0.00..17200038808.85 rows=786226 width=0)
         Filter: (NOT (subplan))
         SubPlan
           ->  Seq Scan on zstore  (cost=0.00..19747.74 rows=851574 width=21)
(5 rows)
-----

select count(foo) from (select product_code from zstore_temp except
select product_code from zstore) as foo;

                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------
 Aggregate  (cost=502201.07..502201.07 rows=1 width=32)
   ->  Subquery Scan foo  (cost=487050.91..501595.06 rows=242403 width=32)
         ->  SetOp Except  (cost=487050.91..499171.03 rows=242402 width=21)
               ->  Sort  (cost=487050.91..493110.97 rows=2424025 width=21)
                     Sort Key: product_code
                     ->  Append  (cost=0.00..79125.50 rows=2424025 width=21)
                           ->  Subquery Scan "*SELECT* 1"
(cost=0.00..50862.02 rows=1572451 width=21)
                                 ->  Seq Scan on zstore_temp
(cost=0.00..35137.51 rows=1572451 width=21)
                           ->  Subquery Scan "*SELECT* 2"
(cost=0.00..28263.48 rows=851574 width=21)
                                 ->  Seq Scan on zstore
(cost=0.00..19747.74 rows=851574 width=21)

----

select count(zstore_temp.product_code) from zstore_temp left join
zstore on zstore_temp.product_code = zstore.product_code where
zstore.product_code is null;


                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Aggregate  (cost=100965813.69..100965813.69 rows=1 width=21)
   ->  Merge Right Join  (cost=448485.57..100882122.54 rows=33476460 width=21)
         Merge Cond: ("outer"."?column2?" = "inner"."?column2?")
         Filter: ("outer".product_code IS NULL)
         ->  Sort  (cost=154686.81..156815.74 rows=851574 width=21)
               Sort Key: (zstore.product_code)::text
               ->  Seq Scan on zstore  (cost=0.00..19747.74
rows=851574 width=21)
         ->  Sort  (cost=293798.76..297729.88 rows=1572451 width=21)
               Sort Key: (zstore_temp.product_code)::text
               ->  Seq Scan on zstore_temp  (cost=0.00..35137.51
rows=1572451 width=21)


-----

It seems as if there are multiple order-of-magnitude differences
between these queries given the above costs:

17200040774
100965813
502201

Is there anything that can be done to improve the performance of the
subquery or left join strategies?

Thanks, and happy Thanksgiving to all the USians,
--
David N. Welton
 - http://www.dedasys.com/davidw/

Apache, Linux, Tcl Consulting
 - http://www.dedasys.com/

David Welton <davidnwelton@gmail.com> writes:
> I'm curious as to what the general opinion is on these different
> queries and their relative merits, especially in terms of speed:

You really should do EXPLAIN ANALYZE rather than assuming that the
planner's estimates are reliable.

The NOT IN case, at least, could be improved by increasing work_mem
to the point where the subquery fits into an in-memory hash table.
Larger work_mem would probably help the sorts used in the other
plans too.

            regards, tom lane