Sean Rhea <sean.c.rhea@xxxxxxxxx> writes: > No, the customers table is not 100% the same. This is a live production > system, so the data is (unfortunately) changing under us a bit here. That > said, there are still some strange things going on. I just reran > everything. The query plan time hasn't changed, but as Jeremy, Igor, and > David all pointed out, there's something funky going on with the apparent > size of the customers table. These queries were all run within 5 minutes of > each other: > production=> explain analyze SELECT ac.* FROM balances ac JOIN customers o > ON (o.id= ac.customer_id AND o.group_id = 45); > QUERY > PLAN > ----------------------------------------------------------------------------------------------------------------------------------------------------------- > Merge Join (cost=2475.89..20223.08 rows=7 width=80) (actual > time=157.437..243670.853 rows=7318 loops=1) > Merge Cond: (ac.customer_id = o.id) -> Index Scan using > balances_customer_id_index on balances ac (cost=0.00..727.42 rows=16876 > width=80) (actual time=0.489..30.573 rows=16876 loops=1) > -> Index Scan using customers_pkey on customers o (cost=0.00..65080.01 > rows=184 width=8) (actual time=127.266..243582.767 rows=*7672* loops=1) > Filter: (group_id = 45) > Rows Removed by Filter: *212699113* > Total runtime: 243674.288 ms > (7 rows) > production=> select count(*) from customers where group_id = 45; > count > ------- > 430 > (1 row) What you're looking at there is rows being read repeatedly as a consequence of the mergejoin applying mark/restore operations to rescan portions of its righthand input. This will happen whenever there are duplicate keys in the lefthand input. I think the planner does take the possibility of rescans into account in its cost estimates, but perhaps it's not weighing it heavily enough. It would be interesting to see what you get as a second-choice plan if you set enable_mergejoin = off. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
