Hi Laurenz,
Some of the partition scans are quick (those with nothing to return), but my point is that the scan PER TABLE is
significantlyslower than if I call the hypertable than if I call the table directly. THIS SHOULDN'T BE THE CASE. Most
tablesare 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
thistime 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.albe@cybertec.at]
Sent: Wednesday, February 14, 2018 11:00 PM
To: Stephen Froehlich <s.froehlich@cablelabs.com>; pgsql-novice@postgresql.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