Обсуждение: Query performance issue

Nagaraj Raj schrieb am 04.09.2020 um 23:18:
> I have a query which will more often run on DB and very slow and it
> is doing 'seqscan'. I was trying to optimize it by adding indexes in
> different ways but nothing helps.
>
> EXPALIN ANALYZE select serial_no,receivingplant,sku,r3_eventtime
> from (select serial_no,receivingplant,sku,eventtime as r3_eventtime, row_number() over (partition by serial_no order
byeventtime desc) as mpos 
> from receiving_item_delivered_received
> where eventtype='LineItemdetailsReceived'
> and replenishmenttype = 'DC2SWARRANTY'
> and coalesce(serial_no,'') <> ''
> ) Rec where mpos = 1;
>
>
> Query Planner:
>
> "Subquery Scan on rec  (cost=70835.30..82275.49 rows=1760 width=39) (actual time=2322.999..3451.783 rows=333451
loops=1)"
> "  Filter: (rec.mpos = 1)"
> "  Rows Removed by Filter: 19900"
> "  ->  WindowAgg  (cost=70835.30..77875.42 rows=352006 width=47) (actual time=2322.997..3414.384 rows=353351
loops=1)"
> "        ->  Sort  (cost=70835.30..71715.31 rows=352006 width=39) (actual time=2322.983..3190.090 rows=353351
loops=1)"
> "              Sort Key: receiving_item_delivered_received.serial_no, receiving_item_delivered_received.eventtime
DESC"
> "              Sort Method: external merge  Disk: 17424kB"
> "              ->  Seq Scan on receiving_item_delivered_received  (cost=0.00..28777.82 rows=352006 width=39) (actual
time=0.011..184.677rows=353351 loops=1)" 
> "                    Filter: (((COALESCE(serial_no, ''::character varying))::text <> ''::text) AND ((eventtype)::text
='LineItemdetailsReceived'::text) AND ((replenishmenttype)::text = 'DC2SWARRANTY'::text))" 
> "                    Rows Removed by Filter: 55953"
> "Planning Time: 0.197 ms"
> "Execution Time: 3466.985 ms"

The query retrieves nearly all rows from the table 353351 of 409304 and the Seq Scan takes less than 200ms, so that's
notyour bottleneck. 
Adding indexes won't change that.

The majority of the time is spent in the sort step which is done on disk.
Try to increase work_mem until the "external merge" disappears and is done in memory.

Thomas

On Fri, Sep 04, 2020 at 09:18:41PM +0000, Nagaraj Raj wrote:
> I have a query which will more often run on DB and very slow and it is doing 'seqscan'. I was trying to optimize it
byadding indexes in different ways but nothing helps.
 
>Any suggestions?
>

1) It's rather difficult to read the query plan as it's mangled by your
e-mail client. I recommend to check how to prevent the client from doing
that, or attaching the plan as a file.

2) The whole query takes ~3500ms, and the seqscan only accounts for
~200ms, so it's very clearly not the main issue.

3) Most of the time is spent in sort, so the one thing you can do is
either increasing work_mem, or adding index providing that ordering.
Even better if you include all necessary columns to allow IOS.


regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Sat, 5 Sep 2020 at 10:20, Nagaraj Raj <nagaraj.sf@yahoo.com> wrote:
> I added the index as you suggested and the planner going through the bitmap index scan,heap and the new planner is,
> HaOx | explain.depesz.com

In addition to that index, you could consider moving away from
standard SQL and use DISTINCT ON, which is specific to PostgreSQL and
should give you the same result.

EXPLAIN ANALYZE
SELECT DISTINCT ON (serial_no) serial_no,receivingplant,sku,r3_eventtime
FROM receiving_item_delivered_received
WHERE eventtype='LineItemdetailsReceived'
  AND replenishmenttype = 'DC2SWARRANTY'
  AND coalesce(serial_no,'') <> ''
ORDER BY serial_no,eventtime DESC;

The more duplicate serial_nos you have the better this one should
perform.  It appears you don't have too many so I don't think this
will be significantly faster, but it should be a bit quicker.

David

Hi %,

in order to be able to readjust the effects of the stored procedure and, if necessary, 
to save turnaround times, different requests can be concatenated using semicolons for
bundling several statements in one request. We did some tests against a postgres cluster.

The results in terms of optimizations are as follows:


Batchsize  | clients|  count Queries | average s/query| comment
--------------|---------|----------------------|----------------------|-------------------
1         | 1        |  15.86k         |  2.24ms               | 
10         | 1        |  31.80k         |  332us               | 
25         | 1        |  31.75k         |  312us               | 
50         | 1        |  32.00k         |  280us               | 
100         | 1        |  32.00k         |  286us               | 
          |          |                |                                | 
1         | 2        |  57.1k         |  733us               | Drop to 30k after some time!!
10         | 2        |  63.6k         |  323us               | 
25         | 2        |  63.5k         |  308us               | 
50         | 2        |  64k         |  293us               | 
100         | 2        |  67.2k         |  290us               | 
                   |           |                            |                               | 
1         | 10        |  158.6k         |  2.15ms               |     
10         | 10        |  298.9k         |  383us               | Drop to ~200k!!
25         | 10        |  225k         |  1.16ms               | 
50         | 10        |  192k         |  1.55ms               | 
100         | 10        |  201.6k         |  1.44ms               | 
          |          |                         |                                 | 
10         | 50        |  800k                 |  2.2ms               | 


It seems to be saturated here at around 200k requests per minute, 
the question remains why this is so.

Does anyone has experience with something similar or are there some
hints about how to optimize the postgres cluster for such bundled statements?

Thanks and best regards

Dirk