I mailed last month [0] but didn't see any reponse .. (if I'm being naive, daft, or missing something simple, please just say so).
[0] https://www.postgresql.org/message-id/20170326193344.GS31628%40telsasoft.com It seems when self (inner/equi) joining there's two bad alternatives: either specify a where clause for each self-joined table and incur poor estimate and plan, due to incorrect perceived independence of clauses, even though joined column(s) could/ought to be known equal; or, specify where clause only once, and incur cost of joining across all partitions, due to no contraint exclusion on one (or more) self-joined table heirarchy/s. -- Specify WHERE for each table causes bad underestimate: |ts=# explain analyze SELECT * FROM eric_enodeb_metrics a JOIN eric_enodeb_metrics b USING (start_time, site_id) WHERE a.start_time>='2017-03-19' AND a.start_time<'2017-03-20' AND b.start_time>='2017-03-19' AND b.start_time<'2017-03-20'; | Hash Join (cost=7310.80..14680.86 rows=14 width=1436) (actual time=33.053..73.180 rows=7869 loops=1) | Hash Cond: ((a.start_time = b.start_time) AND (a.site_id = b.site_id)) | -> Append (cost=0.00..7192.56 rows=7883 width=723) (actual time=1.394..19.414 rows=7869 loops=1) | -> Seq Scan on eric_enodeb_metrics a (cost=0.00..0.00 rows=1 width=718) (actual time=0.003..0.003 rows=0 loops=1) | Filter: ((start_time >= '2017-03-19 00:00:00-04'::timestamp with time zone) AND (start_time < '2017-03-20 00:00:00-04'::timestamp with time zone)) | -> Bitmap Heap Scan on eric_enodeb_201703 a_1 (cost=605.34..7192.56 rows=7882 width=723) (actual time=1.390..14.536 rows=7869 loops=1) | Recheck Cond: ((start_time >= '2017-03-19 00:00:00-04'::timestamp with time zone) AND (start_time < '2017-03-20 00:00:00-04'::timestamp with time zone)) | Heap Blocks: exact=247 | -> Bitmap Index Scan on eric_enodeb_201703_unique_idx (cost=0.00..603.37 rows=7882 width=0) (actual time=1.351..1.351 rows=7869 loops=1) | Index Cond: ((start_time >= '2017-03-19 00:00:00-04'::timestamp with time zone) AND (start_time < '2017-03-20 00:00:00-04'::timestamp with time zone)) | -> Hash (cost=7192.56..7192.56 rows=7883 width=723) (actual time=31.620..31.620 rows=7869 loops=1) | Buckets: 8192 Batches: 1 Memory Usage: 1986kB | -> Append (cost=0.00..7192.56 rows=7883 width=723) (actual time=0.902..19.543 rows=7869 loops=1) | -> Seq Scan on eric_enodeb_metrics b (cost=0.00..0.00 rows=1 width=718) (actual time=0.002..0.002 rows=0 loops=1) | Filter: ((start_time >= '2017-03-19 00:00:00-04'::timestamp with time zone) AND (start_time < '2017-03-20 00:00:00-04'::timestamp with time zone)) | -> Bitmap Heap Scan on eric_enodeb_201703 b_1 (cost=605.34..7192.56 rows=7882 width=723) (actual time=0.899..14.353 rows=7869 loops=1) | Recheck Cond: ((start_time >= '2017-03-19 00:00:00-04'::timestamp with time zone) AND (start_time < '2017-03-20 00:00:00-04'::timestamp with time zone)) | Heap Blocks: exact=247 | -> Bitmap Index Scan on eric_enodeb_201703_unique_idx (cost=0.00..603.37 rows=7882 width=0) (actual time=0.867..0.867 rows=7869 loops=1) | Index Cond: ((start_time >= '2017-03-19 00:00:00-04'::timestamp with time zone) AND (start_time < '2017-03-20 00:00:00-04'::timestamp with time zone)) -- Specify WHERE once gets good estimate, but with unnecessary scan of all child partitions: |ts=# explain analyze SELECT * FROM eric_enodeb_metrics a JOIN eric_enodeb_metrics b USING (start_time, site_id) WHERE start_time>='2017-03-19' AND start_time<'2017-03-20'; | Gather (cost=8310.80..316545.60 rows=9591 width=1427) (actual time=9012.967..9073.539 rows=7869 loops=1) | Workers Planned: 3 | Workers Launched: 3 | -> Hash Join (cost=7310.80..314586.50 rows=3094 width=1427) (actual time=8892.121..8937.245 rows=1967 loops=4) | Hash Cond: ((b.start_time = a.start_time) AND (b.site_id = a.site_id)) | -> Append (cost=0.00..261886.54 rows=2015655 width=714) (actual time=11.464..8214.063 rows=1308903 loops=4) | -> Parallel Seq Scan on eric_enodeb_metrics b (cost=0.00..0.00 rows=1 width=718) (actual time=0.001..0.001 rows=0 loops=4) | -> Parallel Seq Scan on eric_enodeb_201510 b_1 (cost=0.00..10954.43 rows=60343 width=707) (actual time=11.460..258.852 rows=46766 loops=4) | -> Parallel Seq Scan on eric_enodeb_201511 b_2 (cost=0.00..10310.91 rows=56891 width=707) (actual time=18.395..237.841 rows=44091 loops=4) |[...] | -> Parallel Seq Scan on eric_enodeb_201703 b_29 (cost=0.00..6959.75 rows=81875 width=723) (actual time=0.017..101.969 rows=49127 loops=4) | -> Hash (cost=7192.56..7192.56 rows=7883 width=723) (actual time=51.843..51.843 rows=7869 loops=4) | Buckets: 8192 Batches: 1 Memory Usage: 1970kB | -> Append (cost=0.00..7192.56 rows=7883 width=723) (actual time=2.558..27.829 rows=7869 loops=4) | -> Seq Scan on eric_enodeb_metrics a (cost=0.00..0.00 rows=1 width=718) (actual time=0.014..0.014 rows=0 loops=4) | Filter: ((start_time >= '2017-03-19 00:00:00-04'::timestamp with time zone) AND (start_time < '2017-03-20 00:00:00-04'::timestamp with time zone)) | -> Bitmap Heap Scan on eric_enodeb_201703 a_1 (cost=605.34..7192.56 rows=7882 width=723) (actual time=2.542..17.305 rows=7869 loops=4) | Recheck Cond: ((start_time >= '2017-03-19 00:00:00-04'::timestamp with time zone) AND (start_time < '2017-03-20 00:00:00-04'::timestamp with time zone)) | Heap Blocks: exact=247 | -> Bitmap Index Scan on eric_enodeb_201703_unique_idx (cost=0.00..603.37 rows=7882 width=0) (actual time=2.494..2.494 rows=7869 loops=4) | Index Cond: ((start_time >= '2017-03-19 00:00:00-04'::timestamp with time zone) AND (start_time < '2017-03-20 00:00:00-04'::timestamp with time zone)) Minor variations have same problems; -- Scans all partitions: ts=# explain analyze SELECT * FROM (SELECT * FROM eric_enodeb_metrics a) t1 JOIN (SELECT * FROM eric_enodeb_metrics b WHERE start_time>='2017-03-19 23:00:00' AND start_time<'2017-03-20') t2 USING (start_time, site_id); -- Underestimtes due to perceived independence of clause: |ts=# explain analyze SELECT * FROM (SELECT * FROM eric_enodeb_metrics a WHERE start_time>='2017-03-19' AND start_time<'2017-03-20') t1 JOIN (SELECT * FROM eric_enodeb_metrics b WHERE start_time>='2017-03-19' AND start_time<'2017-03-20') t2 USING (start_time, site_id); | Hash Join (cost=7308.59..14676.41 rows=14 width=1436) (actual time=30.352..64.004 rows=7869 loops=1) Thank you in advance for your any response. Justin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
