Re: Many DataFileRead - IO waits

> Thanks for the suggestion. Yes I could change the sql and when using only
> one filter for int_otherid2 it does use all 3 columns as the index key.

explain (analyze,buffers) SELECT
uuid_id,int_id,timestamp_date,int_otherid,float_value,int_otherid2,int_otherid3,int_rowver
FROM tabledata WHERE dtdatetime <= '2020-01-20 03:05:00.013' AND
gDiagnosticId IN ('3c99d61b-21a1-42ea-92a8-3cc88d79f3f1') AND
ivehicleid=8149 ORDER BY dtdatetime DESC LIMIT 1

QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.71..85.13 rows=1 width=84) (actual time=300.820..300.821
rows=1 loops=1)
Buffers: shared hit=17665 read=1
-> Index Scan Backward using
ix_tabledata_intid_timestampdate_intotherid3_intotherid2 on tabledata
(cost=0.71..41960.39 rows=497 width=84) (actual time=300.808..300.809
rows=1 loops=1)
Index Cond: ((int_id = 8149) AND (timestamp_date <= '2020-01-20
03:05:00.013'::timestamp without time zone) AND (int_otherid2 =
'3c99d61b-21a1-42ea-92a8-3cc88d79f3f1'::uuid))
Buffers: shared hit=17665 read=1
Planning time: 58.769 ms
Execution time: 300.895 ms
(7 rows)

> I still haven't been able to explain why this changed all of a sudden (I
> am
> working on reproducing this error in a test environment) but this could be
> a good workaround. I might be able to just make 6 calls or maybe rewrite
> the original query some other way in order to get it to use all 3 keys of
> the index. I'll have to do some more testing

Parsing of 58 ms and 300 ms for 17665 memory blocks read is very very bad
...
Are those shared buffers in memory or SWAPPED ?
Is the server CPU bounded or limited ?

May be you should dump some data for a test case on an other platform
(any desktop) to get a comparison point

Regards
PAscal

--
Sent from: https://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html