|
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 |
