From: Tom Lane <tgl@sss.pgh.pa.us>
To: Emil Iggland <emil.iggland@metrima.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance differential when 0 values present vs when 1 values present. Planner return 52k rows when 0 expected.
Date: Mon, 25 Apr 2022 12:00:21 -0400
Message-ID: <87994.1650902421@sss.pgh.pa.us> (raw)
In-Reply-To: <4eacd5f7-1f8d-1e0d-ad9f-61f6c5ab706e@metrima.com>
References: <4eacd5f7-1f8d-1e0d-ad9f-61f6c5ab706e@metrima.com>
Emil Iggland <emil.iggland@metrima.com> writes:
> The query that is giving us issues is the following, channel 752433 has
> NO values, 752431 has values.
> (Channel 752433 only has valueseries 752434)
> select * from datavalue
> where dataview in ( select id from valueseries where channel =
> %channel_idx%)
> ORDER BY VALUETIMESTAMP DESC
> FETCH FIRST ROW only;
> Running explain analyze shows strange numbers, 52'000 rows are being
> returned but there are no rows there.
> For channel 752433
> -> Index Scan Backward using
> datavalue_2022_03_valuetimestamp_dataview_idx on datavalue_2022_03
> datavalue_6 (cost=0.42..7166.19 rows=119673 width=226) (actual
> time=0.008..32.831 rows=119601 loops=1)
You've got the wrong column order (for this query anyway) in that
index. It'd work a lot better if dataview were the first column;
or at least, it wouldn't tempt the planner to try this unstably-
performing plan. It's trying to use the index ordering to satisfy
the ORDER BY, which works great as long as it finds a dataview
match in some reasonably recent index entry. Otherwise, it's
going to crawl the whole index to discover that there's no match.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, emil.iggland@metrima.com
Subject: Re: Performance differential when 0 values present vs when 1 values present. Planner return 52k rows when 0 expected.
In-Reply-To: <87994.1650902421@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox