Optimizing SQL query with two table joins and over 40 million rows in partitioned table

Question

I have three tables, lets say their names are:

• table1 (has total over 40 million rows, but is partitioned with Declarative partition https://www.postgresql.org/docs/10/static/ddl-partitioning.html based on timestamp (1 month per table ~5 million rows in one partitioned table)
• table2 has ~ 200k rows
• table3 has ~ 1000 rows

My query is as follows:

SELECT table1.timestamp::DATE, table2.segment, COUNT(table1.timestamp::DATE) as count, ABS(SUM(table1.amount)) as sum, table3.category, table3.text

FROM table1 
LEFT JOIN table3 ON table1.id = table3.id 
LEFT JOIN table2 ON table1.hash = table2.hash

WHERE table1.timestamp::DATE >= '2015-01-01' AND table1.time::DATE < '2015-02-01' AND table2.segment IN (1,2)

GROUP BY (table1.timestamp::DATE, table2.segment, table3.category, table3.text) 

ORDER BY table1.timestamp::DATE, sum DESC;

How it would be best to optimize the query? I have indexes on the following columns:

• table1.hash
• table1.timestamp
• table1.id
• table2.hash
• table2.segment
• table3.id

The query takes over 2 minutes to complete currently. I had a table1 with 5 million rows before and this query was running under 5 seconds. But when it grew to 40 million it got slow. So I partitioned it and would have assumed with the indexes and table1 partitioned into 5 million rows partition tables the performance would increase, but nothing. It is like there is no difference if the table is partitioned or not.

To my knowledge WHERE clause should be executing first in limiting the results so that the JOIN operations should only join already limited amount of rows. But this does not seem to happen?

EXPLAIN (ANALYSE, BUFFERS) ouput:

Sort  (cost=1445540.21..1445756.82 rows=86644 width=72) (actual timestampe=142452.118..142452.408 rows=1478 loops=1)
  Sort Key: ((table1_y2015m01.timestamp)::date), (abs(sum(table1_y2015m01.amo))) DESC
  Sort Method: quicksort  Memory: 233kB
  Buffers: shared hit=5799 read=356526, temp read=19671 written=19705
  ->  Finalize GroupAggregate  (cost=1422500.64..1434878.19 rows=86644 width=72) (actual timestampe=140835.242..142450.099 rows=1478 loops=1)
        Group Key: ((table1_y2015m01.timestamp)::date), table2.segment, table3.category, table3.text
        Buffers: shared hit=5796 read=356526, temp read=19671 written=19705
        ->  Gather Merge  (cost=1422500.64..1432098.35 rows=72204 width=72) (actual timestampe=140835.206..142436.056 rows=4341 loops=1)
              Workers Planned: 2
              Workers Launched: 2
              Buffers: shared hit=5796 read=356526, temp read=19671 written=19705
              ->  Partial GroupAggregate  (cost=1421500.62..1422764.19 rows=36102 width=72) (actual timestampe=140743.601..142322.856 rows=1447 loops=3)
                    Group Key: ((table1_y2015m01.timestamp)::date), table2.segment, table3.category, table3.text
                    Buffers: shared hit=17585 read=1072876, temp read=60612 written=60717
                    ->  Sort  (cost=1421500.62..1421590.88 rows=36102 width=46) (actual timestampe=140743.582..141464.826 rows=1104645 loops=3)
                          Sort Key: ((table1_y2015m01.timestamp)::date), table2.segment, table3.category, table3.text
                          Sort Method: external merge  Disk: 59688kB
                          Buffers: shared hit=17585 read=1072876, temp read=60612 written=60717
                          ->  Hash Left Join  (cost=9762.14..1418767.74 rows=36102 width=46) (actual timestampe=149.558..138457.224 rows=1104645 loops=3)
                                Hash Cond: (table1_y2015m01.id = table3.id)
                                Buffers: shared hit=17459 read=1072876, temp read=22247 written=22241
                                ->  Hash Join  (cost=9717.06..1418176.95 rows=36102 width=22) (actual timestampe=148.136..137715.646 rows=1104645 loops=3)
                                      Hash Cond: (table1_y2015m01.hash = table2.hash)
                                      Buffers: shared hit=17397 read=1072853, temp read=22247 written=22241
                                      ->  Append  (cost=0.00..1404560.77 rows=82919 width=48) (actual timestampe=0.875..134500.770 rows=1698510 loops=3)
                                            Buffers: shared read=1072853
                                            ->  Parallel Seq Scan on table1_y2015m01  (cost=0.00..183609.75 rows=10616 width=48) (actual timestampe=0.874..14278.069 rows=1698510 loops=3)
                                                  Filter: (((timestamp)::date >= '2015-01-01'::date) AND ((timestamp)::date < '2015-02-01'::date))
                                                  Buffers: shared read=141147
                                            ->  Parallel Seq Scan on table1_y2015m02  (cost=0.00..176631.60 rows=10196 width=48) (actual timestampe=17385.085..17385.085 rows=0 loops=3)
                                                  Filter: (((timestamp)::date >= '2015-01-01'::date) AND ((timestamp)::date < '2015-02-01'::date))
                                                  Rows Removed by Filter: 1631344
                                                  Buffers: shared read=135848
                                            ->  Parallel Seq Scan on table1_y2015m03  (cost=0.00..198820.02 rows=11823 width=48) (actual timestampe=19423.992..19423.992 rows=0 loops=3)
                                                  Filter: (((timestamp)::date >= '2015-01-01'::date) AND ((timestamp)::date < '2015-02-01'::date))
                                                  Rows Removed by Filter: 1891761
                                                  Buffers: shared read=151526
                                            ->  Parallel Seq Scan on table1_y2015m04  (cost=0.00..202131.93 rows=12033 width=48) (actual timestampe=19706.615..19706.615 rows=0 loops=3)
                                                  Filter: (((timestamp)::date >= '2015-01-01'::date) AND ((timestamp)::date < '2015-02-01'::date))
                                                  Rows Removed by Filter: 1925357
                                                  Buffers: shared read=153998
                                            ->  Parallel Seq Scan on table1_y2015m05  (cost=0.00..218470.44 rows=13020 width=48) (actual timestampe=21286.843..21286.843 rows=0 loops=3)
                                                  Filter: (((timestamp)::date >= '2015-01-01'::date) AND ((timestamp)::date < '2015-02-01'::date))
                                                  Rows Removed by Filter: 2083298
                                                  Buffers: shared read=166388
                                            ->  Parallel Seq Scan on table1_y2015m06  (cost=0.00..205107.03 rows=12181 width=48) (actual timestampe=20014.800..20014.800 rows=0 loops=3)
                                                  Filter: (((timestamp)::date >= '2015-01-01'::date) AND ((timestamp)::date < '2015-02-01'::date))
                                                  Rows Removed by Filter: 1949001
                                                  Buffers: shared read=156382
                                            ->  Parallel Seq Scan on table1_y2015m07  (cost=0.00..219741.46 rows=13045 width=48) (actual timestampe=21553.531..21553.531 rows=0 loops=3)
                                                  Filter: (((timestamp)::date >= '2015-01-01'::date) AND ((timestamp)::date < '2015-02-01'::date))
                                                  Rows Removed by Filter: 2087138
                                                  Buffers: shared read=167563
                                            ->  Parallel Seq Scan on table1_y2015m08  (cost=0.00..1.01 rows=1 width=76) (actual timestampe=0.157..0.157 rows=0 loops=3)
                                                  Filter: (((timestamp)::date >= '2015-01-01'::date) AND ((timestamp)::date < '2015-02-01'::date))
                                                  Rows Removed by Filter: 0
                                                  Buffers: shared read=1
                                            ->  Parallel Seq Scan on table1_y2015m09  (cost=0.00..11.88 rows=1 width=76) (actual timestampe=0.001..0.001 rows=0 loops=3)
                                                  Filter: (((timestamp)::date >= '2015-01-01'::date) AND ((timestamp)::date < '2015-02-01'::date))
                                            ->  Parallel Seq Scan on table1_y2015m10  (cost=0.00..11.88 rows=1 width=76) (actual timestampe=0.000..0.000 rows=0 loops=3)
                                                  Filter: (((timestamp)::date >= '2015-01-01'::date) AND ((timestamp)::date < '2015-02-01'::date))
                                            ->  Parallel Seq Scan on table1_y2015m11  (cost=0.00..11.88 rows=1 width=76) (actual timestampe=0.000..0.000 rows=0 loops=3)
                                                  Filter: (((timestamp)::date >= '2015-01-01'::date) AND ((timestamp)::date < '2015-02-01'::date))
                                            ->  Parallel Seq Scan on table1_y2015m12  (cost=0.00..11.88 rows=1 width=76) (actual timestampe=0.000..0.000 rows=0 loops=3)
                                                  Filter: (((timestamp)::date >= '2015-01-01'::date) AND ((timestamp)::date < '2015-02-01'::date))
                                      ->  Hash  (cost=8082.77..8082.77 rows=80423 width=34) (actual timestampe=146.734..146.734 rows=81226 loops=3)
                                            Buckets: 65536  Batches: 2  Memory Usage: 3144kB
                                            Buffers: shared hit=17319, temp written=813
                                            ->  Seq Scan on table2  (cost=0.00..8082.77 rows=80423 width=34) (actual timestampe=0.010..89.582 rows=81226 loops=3)
                                                  Filter: (segment = ANY ('{1,2}'::integer[]))
                                                  Rows Removed by Filter: 103556
                                                  Buffers: shared hit=17319
                                ->  Hash  (cost=32.81..32.81 rows=981 width=28) (actual timestampe=1.374..1.374 rows=981 loops=3)
                                      Buckets: 1024  Batches: 1  Memory Usage: 69kB
                                      Buffers: shared hit=46 read=23
                                      ->  Seq Scan on table3  (cost=0.00..32.81 rows=981 width=28) (actual timestampe=0.179..1.000 rows=981 loops=3)
                                            Buffers: shared hit=46 read=23
Planning timestampe: 31.311 ms
Execution timestampe: 144012.186 ms

Answer 1

If you are partitioning on timestamp, but querying on timestamp::date, then that will frustrate the partition exclusion code. Which the execution plan shows.

Rewrite your queries to not cast to date, or change your partition to be on the cast expression rather than the timestamp column itself.