Re: Many DataFileRead - IO waits

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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. 
 
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.


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux