From: legrand legrand <legrand_legrand@hotmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Many DataFileRead - IO waits
Date: Mon, 2 Mar 2020 16:09:53 -0700 (MST)
Message-ID: <1583190593477-0.post@n3.nabble.com> (raw)
In-Reply-To: <CAEPE5bPXKd=kJGfNPW2PQRq241nbTV6KeWksAaHhZSxParVLPw@mail.gmail.com>
References: <CAEPE5bNNkFhoPRufSTrPZVA-W+bbu+LgXUQ1OzD9VwMowV5=VA@mail.gmail.com>
<CAHOFxGrWoQ+o=aDYkeS50n9bgjJn2FCFrFXSsx5DbgUj=mh9Wg@mail.gmail.com>
<CAEPE5bOtH=8dwFyu8MSOiksK56jAH7j_-i6c-_JatFx=6UofYg@mail.gmail.com>
<CAHOFxGrMHhm+kGeB=DP8nYm-H2k2A5CU4gbFqWrtQtD_VETgKg@mail.gmail.com>
<CAEPE5bMHZ3vLS85O7dTFGhRg+SG=Z2=dDbupAmjNAnDEBPayYQ@mail.gmail.com>
<1582916409615-0.post@n3.nabble.com>
<CAEPE5bPXKd=kJGfNPW2PQRq241nbTV6KeWksAaHhZSxParVLPw@mail.gmail.com>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: legrand_legrand@hotmail.com
Subject: Re: Many DataFileRead - IO waits
In-Reply-To: <1583190593477-0.post@n3.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox