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.
In-reply-to: <4eacd5f7-1f8d-1e0d-ad9f-61f6c5ab706e@metrima.com>
References: <4eacd5f7-1f8d-1e0d-ad9f-61f6c5ab706e@metrima.com>
Comments: In-reply-to Emil Iggland <emil.iggland@metrima.com>
	message dated "Fri, 22 Apr 2022 16:53:48 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <87993.1650902421.1@sss.pgh.pa.us>
Date: Mon, 25 Apr 2022 12:00:21 -0400
Message-ID: <87994.1650902421@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/87994.1650902421%40sss.pgh.pa.us>
Precedence: bulk

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