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

I think some tables likely got vacuumed per http://ask.use-the-index-luke.com/questions/148/why-is-this-postgres-query-doing-a-bitmap-heap-scan-after-the-index-scan

I am now explicitly vacuum analyzing-ing each table after a large write.

--Stephen

-----Original Message-----
From: Laurenz Albe [mailto:laurenz(dot)albe(at)cybertec(dot)at]
Sent: Thursday, February 15, 2018 1:59 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

CableLabs WARNING: The sender of this email could not be validated and may not match the person in the "From" field.

Stephen Froehlich wrote:
> 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.

In your complete plan, scanning "raptor_global_bitrate_20171101_cmts1" took only 382.247 microseconds as opposed to 24760.668 in your first e-mail.
Also the strange "loops=6" is not present.

So it is hard to say what was going on there in the first place...

Often caching causes big differences in execution time.

Yours,
Laurenz Albe