weird execution plan - Mailing list pgsql-performance

Hi,

Can someone figure out why the first query runs so slow  comparing to the second one? They generate the same result…

dev=# explain analyze select count(distinct wid) from terms_weekly_20140503 a join port_terms b on a.term=b.terms;

                                                                       QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------

Aggregate  (cost=2226181.12..2226181.13 rows=1 width=516) (actual time=18757.318..18757.319 rows=1 loops=1)

   ->  Hash Join  (cost=37.67..2095240.22 rows=52376358 width=516) (actual time=0.758..2496.190 rows=1067696 loops=1)

         Hash Cond: (a.term = b.terms)

         ->  Seq Scan on terms_weekly_20140503 a  (cost=0.00..240738.81 rows=8516481 width=548) (actual time=0.009..951.875 rows=8516481 loops=1)

         ->  Hash  (cost=22.30..22.30 rows=1230 width=32) (actual time=0.690..0.690 rows=1000 loops=1)

               Buckets: 1024  Batches: 1  Memory Usage: 51kB

               ->  Seq Scan on port_terms b  (cost=0.00..22.30 rows=1230 width=32) (actual time=0.009..0.283 rows=1000 loops=1)

Total runtime: 18757.367 ms

(8 rows)

Time: 18758.068 ms

dev=# explain analyze with x as (select distinct wid from terms_weekly_20140503 a join port_terms b on a.term=b.terms) select count(*) from x;

                                                                           QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

Aggregate  (cost=2226187.62..2226187.63 rows=1 width=0) (actual time=2976.011..2976.011 rows=1 loops=1)

   CTE x

     ->  HashAggregate  (cost=2226181.12..2226183.12 rows=200 width=516) (actual time=2827.958..2896.747 rows=212249 loops=1)

           ->  Hash Join  (cost=37.67..2095240.22 rows=52376358 width=516) (actual time=0.734..2470.533 rows=1067696 loops=1)

                 Hash Cond: (a.term = b.terms)

                 ->  Seq Scan on terms_weekly_20140503 a  (cost=0.00..240738.81 rows=8516481 width=548) (actual time=0.009..916.028 rows=8516481 loops=1)

                 ->  Hash  (cost=22.30..22.30 rows=1230 width=32) (actual time=0.669..0.669 rows=1000 loops=1)

                       Buckets: 1024  Batches: 1  Memory Usage: 51kB

                       ->  Seq Scan on port_terms b  (cost=0.00..22.30 rows=1230 width=32) (actual time=0.009..0.269 rows=1000 loops=1)

   ->  CTE Scan on x  (cost=0.00..4.00 rows=200 width=0) (actual time=2827.961..2963.878 rows=212249 loops=1)

Total runtime: 2980.681 ms

(11 rows)

Thanks,

Suya