Re: Performance differential when 0 values present vs when 1 values present. Planner return 52k rows when 0 expected.

Emil Iggland <emil(dot)iggland(at)metrima(dot)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