0

I have a table for statuses of lots of things on PostgreSQL13.

Because of the number of objects I am keeping tabs on I decided to partition my table into years. According to the pgconf-eu-2019 conference from Postgres 12:

Performance has been improved significantly by rewriting various pieces of code to process only the partitions that are needed by a query. So where previously, single-record queries would run in the amount of time that is proportional to the number of partitions, that is no longer the case.

However when I analyse my query I am finding that table performance is significantly slowed by PostgreSQL scanning each of the child partition tables:

Results form explain analyze, cost, verbose, buffers

Can anyone shed light on why this is the case? A query that should take just a few seconds is timing out my application. Have I misconfigured something? See:

Table description

as an example of a culprit query...

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
select  "videos"."id", "videos"."size", "videos"."name" as file, cast("videos"."duration" as integer), extract(epoch from "videos"."start_timestamp") as start_time,extract(epoch from  "videos"."end_timestamp")as end_time,"videos"."active", "statuses"."name" as status  
from 
  "videos"
left join "statuses" on ("statuses"."model_type" = 'App\Models\Video') and ("statuses"."created_at" between '2020-05-01T00:00:00' and '2021-05-02T23:59:59' )  
where "statuses"."id" = 
(
  select    max("id")
  from    "statuses"
  where   "statuses"."model_id" = "videos"."id") 
  and "videos"."vessel_id" = 26 and "videos"."device_id" = 104   
  and "videos"."end_timestamp" >= '2020-05-01T00:00:00' 
  and "videos"."start_timestamp" <= '2021-05-02T23:59:59'
  group by 
    "videos"."start_timestamp", 
    "videos"."end_timestamp", 
    "videos"."size", "videos"."id", 
    "videos"."duration", 
    "videos"."name", "videos"."active", 
    "statuses"."name"
  order by "videos"."start_timestamp"
);
2
  • 2
    Hi, and welcome to dba.se. Please don't use images here if possible for the reasons outlined in this link. – Vérace Jun 4 at 5:57
  • Your query has a syntax error, unbalanced parentheses. – jjanes Jun 4 at 20:10
1

To get partition pruning, it must be easy for the database to determine which partitions to scan. Essentially, you need a WHERE condition in the query that restricts the partitioning key. Your query doesn't have that.

To be honest, I cannot understand your query at all. You write LEFT JOIN but then turn it into an inner join with the condition on statuses.id. And the subquery leaves me mystified.

Make sure that any query on statuses has WHERE created_at =/</>/BETWEEN in it.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.