SELECT c.my_type || '-' || cs.my_site, cs.id
FROM table2 cs
JOIN table1 c ON cs.id = c.id
WHERE c.status = 'ACTIVE'
EXPLAIN ANALYZE:
Hash Join (cost=927.36..66271.66 rows=1141298 width=24) (actual time=9.221..888.461 rows=825843 loops=1)
Hash Cond: (cs.id = c.id)
-> Seq Scan on table2 cs (cost=0.00..29630.92 rows=1652792 width=21) (actual time=0.006..320.526 rows=1668338 loops=1)
-> Hash (cost=757.70..757.70 rows=13573 width=7) (actual time=8.995..8.995 rows=13044 loops=1)
-> Seq Scan on tabl1 c (cost=0.00..757.70 rows=13573 width=7) (actual time=0.005..6.005 rows=13044 loops=1)
Filter: ((status)::text = 'ACTIVE'::text)
Total runtime: 948.947 ms
vs
select c.my_type || '-' || cs.my_site, cs.id
from (SELECT id, my_site FROM table1 WHERE status = 'ACTIVE') as c
join table2 cs on cs.id = c.id
EXPLAIN ANALYZE
Hash Join (cost=927.36..66271.66 rows=1141298 width=24) (actual time=8.927..894.598 rows=821287 loops=1)
Hash Cond: (cs.id = c.id)
-> Seq Scan on table2 cs (cost=0.00..29630.92 rows=1652792 width=21) (actual time=0.005..331.769 rows=1663782 loops=1)
-> Hash (cost=757.70..757.70 rows=13573 width=7) (actual time=8.706..8.706 rows=13044 loops=1)
-> Seq Scan on table1 (cost=0.00..757.70 rows=13573 width=7) (actual time=0.005..6.044 rows=13044 loops=1)
Filter: ((status)::text = 'ACTIVE'::text)
Total runtime: 954.941 ms
I would expect the second query to be relatively faster than the first.. but it doesn't seem to be. Keep in mind that these are very large tables.
EXPLAIN
or get an execution plan, put the results in the question as well. If you have no indexes, visit use-the-index-luke.com ASAP. – Andy Lester May 30 '14 at 15:32status = 'ACTIVE'
– wildplasser May 30 '14 at 15:35