0

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?

Improve this question
5
  • I usually ignore the cost. Is 167ms too slow? How fast do you need that query to be?
    – a_horse_with_no_name
    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.
    – a_horse_with_no_name
    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

0

Reset to default

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.