RE: Bitmap Heap Scan taking ~60x as long for table when queried as partition

Hi Laurenz,

Some of the partition scans are quick (those with nothing to return), but my point is that the scan PER TABLE is significantly slower than if I call the hypertable than if I call the table directly. THIS SHOULDN'T BE THE CASE. Most tables are scanned quickly and return nothing.

There IS a combined index on client_ip_md5, start_time ... its my primary key for all of these tables.

Also, the scans are typically parallelized (go back in the thread to the original excerpt), the only reason why not this time is that the server was busy with a backup. Its still much slower when calling the hypertable than the table directly. The parallelization is usually my first clue that an index scan is not being used but instead a heap scan.

--Stephen

-----Original Message-----
From: Laurenz Albe [mailto:laurenz(dot)albe(at)cybertec(dot)at]
Sent: Wednesday, February 14, 2018 11:00 PM
To: Stephen Froehlich <s(dot)froehlich(at)cablelabs(dot)com>; pgsql-novice(at)postgresql(dot)org
Subject: Re: Bitmap Heap Scan taking ~60x as long for table when queried as partition

Stephen Froehlich wrote:
> It took me until this morning to get the EXPLAIN ANALYZE running.
> Note that it is a fair amount slower today because I'm in the midst of
> a pg_dump for the database in question, but the proportions should be there:

That query has to scan all 122 partitions to get its result.
This cannot be avoided, because there are matching rows in many partitions.
Partitioning doesn't help if all partitions have to be scanned.

I notice that there is no parallelization - having several workers scan partitions in parallel should help.

You could create a combined index on (client_ip_md5, start_time), that might speed up the index scans.

Yours,
Laurenz Albe