Re: demystifying nested loop vs. merge join query plan choice

[Sandeep Gupta <gupta.sandeep@xxxxxxxxx>]

Hi Pavel,

 Yes. The postgresql.conf is exactly the same.  The have the same index and clustering and are on the same compute node as well but running on different ports. 

-Sandeep

On Wed, Jul 31, 2013 at 3:14 PM, Pavel Stehule <pavel.stehule@xxxxxxxxx> wrote:

Hello

do you have same configuration?

Regards

Pavel

2013/7/31 Sandeep Gupta <gupta.sandeep@xxxxxxxxx>:

> I have two  postgres instances each with a database of same schema. The
> dataset in both is ''same'' but for randomness i.e.  both contain two tables
> pc(did) and tc(pid, did) that have almost
> same number of rows and have been generate from same distribution.
>
> However the query plan for the join turns out to be completely different: on
> one join takes 2.3 secs while on the other it takes 7 secs.
>
>
> Here are the statistics:
>
> for database 1:
>      size of tc table:  49987585
>      size of pc table: 499616
>
> join plan:
>
> QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------------------------------------------
>  Aggregate  (cost=1534125.08..1534125.09 rows=1 width=0) (actual
> time=8473.296..8473.296 rows=1 loops=1)
>    ->  Merge Join  (cost=2.48..1514765.90 rows=7743672 width=0) (actual
> time=0.084..8409.065 rows=998038 loops=1)
>          Merge Cond: (pc.did = tc.did)
>          ->  Index Only Scan using pc_did_idx on pc  (cost=0.00..12987.04
> rows=499616 width=4) (actual time=0.016..58.202 rows=499616 loops=1)
>                Heap Fetches: 0
>          ->  Index Only Scan using tc_did_idx on tc  (cost=0.00..1298125.32
> rows=49987616 width=4) (actual time=0.014..5141.809 rows=49997291 loops=1)
>                Heap Fetches: 0
>  Total runtime: 8473.337 ms
> '
>
> Query Running time:  5135
>
>
> for database 2:
>   size of tc table: 50012415
>   size of pc table: 500384
>
>                                                                    QUERY
> PLAN
> ------------------------------------------------------------------------------------------------------------------------------------------------
>  Aggregate  (cost=35279895.52..35279895.53 rows=1 width=0) (actual
> time=2501.970..2501.970 rows=1 loops=1)
>    ->  Nested Loop  (cost=0.00..35276697.82 rows=1279080 width=0) (actual
> time=0.038..2418.766 rows=1000834 loops=1)
>          ->  Index Only Scan using pc_did_idx on pc  (cost=0.00..15224.56
> rows=500384 width=4) (actual time=0.017..109.080 rows=500384 loops=1)
>                Heap Fetches: 500384
>          ->  Index Only Scan using tc_did_idx on tc  (cost=0.00..70.44
> rows=3 width=4) (actual time=0.004..0.004 rows=2 loops=500384)
>                Index Cond: (did = pc.did)
>                Heap Fetches: 1000834
>  Total runtime: 2502.017 ms
>
> Query running time: 2090.388 ms
>
> My question is why is the query plan so different for two datasets that are
> really exactly the same. And how can i force the plan to be nested index
> scan on
> database 1 .
>
>
> -Sandeep
>