Re: Merge join vs merge semi join against primary key

Sean Rhea <sean.c.rhea@gmail.com> 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