Re: Many DataFileRead - IO waits

On Fri, Feb 28, 2020 at 2:00 PM legrand legrand <legrand_legrand(at)hotmail(dot)com>
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.

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

Thanks again.