I have a partitioned table as follows:

create table daily_summary
    id                 uuid           default uuid_generate_v4() not null
        constraint daily_summary_partition_pkey
            primary key,
    person_licence     varchar(25),
    fuel_quantity      numeric(10, 3),
    start_date         timestamp                                 not null,
    end_date           timestamp,
    person_enabled     boolean                                   not null,
    start_location     text,
    end_location       text,
    organisation_licence       uuid,
    active             boolean        default false

create table daily_summary_201912
    organisation_licence uuid,
    active       boolean default false,
    constraint daily_summary_201912_pkey
        primary key (id),
    constraint fk_daily_summary_machine_licence_201912
        foreign key (machine_licence) references machine_has_licence,
    constraint daily_summary_201912_start_date_check
        check ((start_date >= '2019-12-01'::date) AND (start_date < '2020-01-01'::date))
    inherits (daily_summary);

create index idx_daily_summary_organisation_licence_start_date_201912
    on daily_summary_201912 (organisation_licence, start_date) WHERE active = true;

There are other partitions as well, from 2013-01-01 to future date.

On the above table, I've a simple query:

select *
from daily_summary
where daily_summary.organisation_licence = '03312415-585d-42fd-b185-b0ab91dabf35'
  and daily_summary.active
  and daily_summary.start_date >= timestamp '2019-12-01 '
  and daily_summary.start_date < timestamp '2020-01-01';

The data in each partition is big, in the order of GBs. When I do explain analyze on my query:

Append  (cost=0.00..80822.64 rows=112091 width=322) (actual time=17.877..145.944 rows=113896 loops=1)
  ->  Seq Scan on daily_summary  (cost=0.00..0.00 rows=1 width=684) (actual time=0.002..0.002 rows=0 loops=1)
        Filter: (active AND (start_date >= '2019-12-01 00:00:00'::timestamp without time zone) AND (start_date < '2020-01-01 00:00:00'::timestamp without time zone) AND (organisation_licence = '03312415-585d-42fd-b185-b0ab91dabf35'::uuid))
  ->  Bitmap Heap Scan on daily_summary_201912  (cost=2601.29..80822.64 rows=112090 width=322) (actual time=17.874..104.199 rows=113896 loops=1)
        Recheck Cond: ((organisation_licence = '03312415-585d-42fd-b185-b0ab91dabf35'::uuid) AND active)
        Filter: ((start_date >= '2019-12-01 00:00:00'::timestamp without time zone) AND (start_date < '2020-01-01 00:00:00'::timestamp without time zone))
        Heap Blocks: exact=36057
        ->  Bitmap Index Scan on idx_daily_summary_organisation_licence_start_date_201912  (cost=0.00..2573.27 rows=112112 width=0) (actual time=11.619..11.619 rows=113896 loops=1)
              Index Cond: (organisation_licence = '03312415-585d-42fd-b185-b0ab91dabf35'::uuid)
Planning time: 5.927 ms
Execution time: 167.103 ms

I'm unable to understand the reason why there's a Bitmap Heap scan on the entire table, which is costing a lot, especially when I query across an entire year, the cost is going really high (80822.64x12)! Can anyone help me understand this and also help me optimize my query?

  • I usually ignore the cost. Is 167ms too slow? How fast do you need that query to be? Apr 1, 2020 at 5:05
  • For one partition, that's acceptable. My queries ranges across partitions ranging few years and I've few joins which make my queries further slow. I wanted to break it down and the cost of my queries seemed too high.
    – Pavanraotk
    Apr 1, 2020 at 9:16
  • Then show us the slow query, not the fast one. Apr 1, 2020 at 9:21
  • Same query across multiple partitions is slow. I'll add the joint query in a new post and may be link it.
    – Pavanraotk
    Apr 1, 2020 at 9:35
  • I'm more curious to understand the high cost and a way to avoid it if possible.
    – Pavanraotk
    Apr 1, 2020 at 9:36


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.