Re: simple query on why a merge join plan got selected

I'd run each query multiple times -- before and after disabling mergejoin --
to ensure that all the data is safely in RAM.

On 12/15/2018 02:13 PM, Vijaykumar Jain wrote:
> Hey Guys,
>
> I was just playing with exploring joins and plans i came across this
>
> create table t1(a int);
> create table t2(a int);
> insert into t1 select (x % 10) from generate_series(1, 100000) x;
> insert into t2 select (x % 100) from generate_series(1, 100000) x;
>
> pgtesting=> analyze t1;
> ANALYZE
>
> pgtesting=> analyze t2;
> ANALYZE
>
>
> this is reproducible
>
> the below query by default makes use of merge join (which takes way
> longer to return rows as compared to when i explicitly disable merge
> join it returns in half the time taken by merge join) but i am not
> able to figure out why, although i have run analyze on the tables.
>
>
> pgtesting=> explain (analyze, buffers) select * from t1 join t2 using (a);
>
> QUERY PLAN
>
> -----------------------------------------------------------------------------------------------------------------------
>
> Merge Join (cost=19495.64..1039705.09 rows=97241600 width=4) (actual
> time=124.153..22243.262 rows=100000000 loops=1)
>
> Merge Cond: (t1.a = t2.a)
>
> Buffers: shared hit=886, temp read=320384 written=616
>
> -> Sort (cost=9747.82..9997.82 rows=100000 width=4) (actual
> time=56.442..81.611 rows=100000 loops=1)
>
> Sort Key: t1.a
>
> Sort Method: external merge Disk: 1376kB
>
> Buffers: shared hit=443, temp read=172 written=173
>
> -> Seq Scan on t1 (cost=0.00..1443.00 rows=100000 width=4)
> (actual time=0.030..10.003 rows=100000 loops=1)
>
> Buffers: shared hit=443
>
> -> Sort (cost=9747.82..9997.82 rows=100000 width=4) (actual
> time=67.702..9469.366 rows=100000001 loops=1)
>
> Sort Key: t2.a
>
> Sort Method: external sort Disk: 1768kB
>
> Buffers: shared hit=443, temp read=220222 written=443
>
> -> Seq Scan on t2 (cost=0.00..1443.00 rows=100000 width=4)
> (actual time=0.013..8.186 rows=100000 loops=1)
>
> Buffers: shared hit=443
>
> Planning time: 0.402 ms
>
> Execution time: 26093.192 ms
>
> (17 rows)
>
>
> pgtesting=> set enable_mergejoin TO FALSE;
>
> SET
>
> pgtesting=> explain (analyze, buffers) select * from t1 join t2 using (a);
>
> QUERY PLAN
>
> ---------------------------------------------------------------------------------------------------------------------
>
> Hash Join (cost=3084.00..1117491.00 rows=97241600 width=4) (actual
> time=26.893..10229.924 rows=100000000 loops=1)
>
> Hash Cond: (t1.a = t2.a)
>
> Buffers: shared hit=889, temp read=273 written=271
>
> -> Seq Scan on t1 (cost=0.00..1443.00 rows=100000 width=4)
> (actual time=0.028..18.123 rows=100000 loops=1)
>
> Buffers: shared hit=443
>
> -> Hash (cost=1443.00..1443.00 rows=100000 width=4) (actual
> time=26.255..26.255 rows=100000 loops=1)
>
> Buckets: 131072 Batches: 2 Memory Usage: 2713kB
>
> Buffers: shared hit=443, temp written=152
>
> -> Seq Scan on t2 (cost=0.00..1443.00 rows=100000 width=4)
> (actual time=0.017..9.163 rows=100000 loops=1)
>
> Buffers: shared hit=443
>
> Planning time: 0.099 ms
>
> Execution time: 14095.975 ms
>
> (12 rows)
>
>
> pgtesting=> show work_mem;
>
> work_mem
>
> ----------
>
> 4MB
>
> (1 row)
>
>
> pgtesting=> show shared_buffers;
>
> shared_buffers
>
> ----------------
>
> 1GB
>
> (1 row)
>
>
> pgtesting=> select version();
>
>
> version
>
> ---------------------------------------------------------------------------------------------------------------------------------------------
>
> PostgreSQL 10.5 (Ubuntu 10.5-1.pgdg16.04+1) on x86_64-pc-linux-gnu,
> compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609,
> 64-bit
>
> (1 row)
>
>
> May be i am missing something way obvious :) but my only concern being
> high cardinality joins may not use the statistics correctly?
>
> Regards,
> Vijay
>

--
Angular momentum makes the world go 'round.