I have a relatively simple problem- i have a table containing a few billion records which is partitioned by record_type_id

if i do select max(data_timestamp) from public.records where record_type_id = 555 it runs fine-

Aggregate  (cost=0.00..0.01 rows=1 width=32) (actual time=0.008..0.009 rows=1 loops=1)
  ->  Result  (cost=0.00..0.00 rows=0 width=0) (actual time=0.002..0.002 rows=0 loops=1)
        One-Time Filter: false
Planning Time: 0.059 ms
Execution Time: 0.052 ms

But if i do the same query getting the record_type_id from a sub query it does a sequential scan on ever parition.

SELECT MAX(data_timestamp) from records where record_type_id =(SELECT id from record_types where name ='sample-name')

Finalize Aggregate  (cost=5045580.35..5045580.36 rows=1 width=8) (actual time=96505.071..96549.777 rows=1 loops=1)
  InitPlan 1 (returns $0)
    ->  Seq Scan on record_type  (cost=0.00..3.73 rows=1 width=8) (actual time=2637.545..2637.546 rows=0 loops=1)
          Filter: (name = 'sample-data'::text)
          Rows Removed by Filter: 57
  ->  Gather  (cost=1001.00..5045576.52 rows=42 width=8) (actual time=2642.550..96549.719 rows=113 loops=1)
        Workers Planned: 7
        Params Evaluated: $0
        Workers Launched: 7
        ->  Parallel Append  (cost=1.00..5044572.32 rows=6 width=8) (actual time=21748.674..93767.100 rows=14 loops=8)
              ->  Partial Aggregate  (cost=1.00..1.01 rows=1 width=8) (actual time=3017.799..3017.800 rows=1 loops=1)
                    ->  Seq Scan on record_285 record_54  (cost=0.00..1.00 rows=1 width=8) (actual time=0.017..0.017 rows=0 loops=1)
                          Filter: (record_type_id = $0)
              ->  Partial Aggregate  (cost=1.00..1.01 rows=1 width=8) (actual time=3221.531..3221.532 rows=1 loops=1)
                    ->  Seq Scan on record_286 record_55  (cost=0.00..1.00 rows=1 width=8) (actual time=0.015..0.015 rows=0 loops=1)
                          Filter: (record_type_id = $0)
....amended for brevity.....
Planning Time: 207.443 ms
  Functions: 5130
"  Options: Inlining true, Optimization true, Expressions true, Deforming true"
"  Timing: Generation 508.129 ms, Inlining 340.793 ms, Optimization 13506.469 ms, Emission 9557.326 ms, Total 23912.717 ms"
Execution Time: 96613.640 ms

Am i missing something? is there a way to get the record_type_id dynamically in a performant way without writing a stored proc?

This is on PG 13.1

  • Literal value is used on the planning stage whereas the subquery output is obtained during execution only.
    – Akina
    Aug 5 at 9:45
  • Avoid subselect, rewrite 2nd query to JOIN form.
    – Akina
    Aug 5 at 9:47
  • @Akina i tried a join and a CTE- still getting the same issue Aug 5 at 9:49
  • 1
    I can't reproduce this. While both partition scans do exist, they get pruned at run time and so labelled with "(never executed)". Can you show the CREATE statements necessary to set up this situation?
    – jjanes
    Aug 5 at 19:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.