Dear mailing list
We are investigating a strange performance issue with our database.
Our use case is a sensor reading database where we have sensor location
(called channels), parameter settings (called valueseries) and reading
(called datavalues). Datavalues is partitioned per month.
Like this: channel <-(FK)- valueseries <-(FK)- datavalues
We have a query that returns the latest sensor reading. When we have no
readings in datavalues the query is SLOW, when we have 1 or more
readings in datavalues the query is FAST. (slow being ~1second, fast
~5ms) It isn't the slowness that is the main problem, but rather the odd
behaviour.
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)
-> Index Scan Backward using
datavalue_2022_04_valuetimestamp_dataview_idx on datavalue_2022_04
datavalue_7 (cost=0.29..4002.79 rows=52499 width=227) (actual
time=0.011..15.005 rows=52499 loops=1)
For channel 752431
-> 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..0.008 rows=1 loops=1)
-> Index Scan Backward using
datavalue_2022_04_valuetimestamp_dataview_idx on datavalue_2022_04
datavalue_7 (cost=0.29..4002.79 rows=52499 width=227) (actual
time=0.011..0.011 rows=1 loops=1)
Inserting even a single row changes the offending rows to the expected
values:
insert into maclient.datavalue (dataview, valuetimestamp, datavalue)
values (752434, '2022-03-01 00:00:00', 234);
-> 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.006..0.006 rows=1 loops=1)
Full explain analyze on https://paste.depesz.com/s/ZwJ
with buffers and track_io_timing: https://paste.depesz.com/s/Ss
Disabling indexscan (set enable_indexscan=false;) hides the problem, it
does not show up with a bitmap index scan.
Running autovacuum analyze doesn't seem to help, the results are the same.
SELECT version();
"PostgreSQL 14.2, compiled by Visual C++ build 1914, 64-bit"
Can anyone explain what is going on here.
* Why is the database returning 52'000+ rows when it should be returning 0?
* Is my query badly formulated?
* Is there something wrong with the indexes and I need to rebuild them?
We are stumped, and would greatly appreciate any input.
Regards
Emil
