On Fri, Feb 28, 2020 at 2:00 PM legrand legrand <legrand_legrand@xxxxxxxxxxx> wrote:
Hello,
I'm not able to use your perfs diagrams,
but it seems to me that not using 3rd column of that index (int_otherid2)
generates an IO problem.
Could you give us the result of
explain (analyze,buffers) SELECT
tabledata.uuid_id,tabledata.int_id,tabledata.timestamp_date,tabledata.int_otherid,tabledata.float_value,tabledata.int_otherid2,tabledata.int_otherid3,tabledata.int_rowver
FROM tabledata
WHERE timestamp_date <= '2020-02-24 03:05:00.013'::timestamp without time
zone
ND int_otherid3 = '3ad2b707-a068-42e8-b0f2-6c8570953760'
AND tabledata.int_id=8149
ORDER BY timestamp_date DESC
LIMIT 1
and this for each value of int_otherid3 ?
and tell us if you are able to change the sql ?
Thanks
Regards
PAscal
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.
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
Thanks again.
