When I reanalyzed the anitmp table with just the 4 entries (2 with
istf=true and 2 with istf=false), both queries then ran the same way/time.
So it would appear, if you want to do a join or a subselect (IN), then
the number of items if will be comparing it to must be less than a
certain number. In my case, the full amount of false's that fail is
actually 40. So if you have a table of 2 million items (in current) and
want to join it to a table of 40 items, then it must do the longer
hashed join version and not the nested loop.
Below are the results with the anitmp table with 42 items. 40 set as
false, and 2 set as true. Is there a way to rewrite my query to have it
run as quick as the others?
Thanks
calldetail=> EXPLAIN ANALYZE SELECT current.* FROM current JOIN anitmp
ON current.orignum=anitmp.ani AND istf=false;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=1.63..1031833.26 rows=256563 width=108) (actual
time=1889.469..155380.749 rows=653 loops=1)
Hash Cond: ("outer".orignum = "inner".ani)
-> Seq Scan on current (cost=0.00..920527.00 rows=10873900
width=108) (actual time=670.402..136192.991 rows=10681150 loops=1)
-> Hash (cost=1.52..1.52 rows=41 width=8) (actual time=0.187..0.187
rows=0 loops=1)
-> Seq Scan on anitmp (cost=0.00..1.52 rows=41 width=8)
(actual time=0.014..0.108 rows=40 loops=1)
Filter: (istf = false)
Total runtime: 155381.960 ms
(7 rows)
calldetail=> EXPLAIN ANALYZE SELECT current.* FROM current JOIN anitmp
ON current.destnum=anitmp.ani AND istf=true;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..5718.45 rows=1413 width=108) (actual
time=76.116..1545.856 rows=1382 loops=1)
-> Seq Scan on anitmp (cost=0.00..1.52 rows=3 width=8) (actual
time=0.025..0.042 rows=2 loops=1)
Filter: (istf = true)
-> Index Scan using i_destnum on current (cost=0.00..1899.74
rows=472 width=108) (actual time=60.133..769.442 rows=691 loops=2)
Index Cond: (current.destnum = "outer".ani)
Total runtime: 1548.321 ms
(6 rows)
