Emil Iggland <emil.iggland@xxxxxxxxxxx> 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
