From: | Alexey M Boltenkov <padrebolt(at)yandex(dot)ru> |
---|---|
To: | Semen Yefimenko <semen(dot)yefimenko(at)gmail(dot)com> |
Cc: | "luis(dot)roberto(at)siscobra(dot)com(dot)br" <luis(dot)roberto(at)siscobra(dot)com(dot)br>, pgsql-performance <pgsql-performance(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Very slow Query compared to Oracle / SQL - Server |
Date: | 2021-05-06 19:58:30 |
Message-ID: | 1302211620330925@mail.yandex.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
<div>Have you try of excluding not null from index? Can you give dispersion of archivestatus?</div><div><br /></div><div><br /></div><div>06.05.2021, 21:59, "Semen Yefimenko" <semen(dot)yefimenko(at)gmail(dot)com>:</div><blockquote><div dir="ltr">Yes, rewriting the query with an IN clause was also my first approach, but I didn't help much. <br />The Query plan did change a little bit but the performance was not impacted.<br /><br /><blockquote style="border:medium;margin:0px 0px 0px 40px;padding:0px"><font face="monospace">CREATE INDEX idx_arcstatus_le1 ON schema.logtable ( archivestatus ) where (archivestatus <= 1)</font><div><font face="monospace">ANALYZE
schema.logtable</font></div></blockquote><div><br />This resulted in this query plan:<br /><br /></div><blockquote style="border:medium;margin:0px 0px 0px 40px;padding:0px"><div><font face="monospace">Gather Merge (cost=344618.96..394086.05 rows=423974 width=2549) (actual time=7327.777..9142.358 rows=516031 loops=1)</font></div><div><font face="monospace"> Output: column1, .. , column54</font></div><div><font face="monospace"> Workers Planned: 2</font></div><div><font face="monospace"> Workers Launched: 2</font></div><div><font face="monospace"> Buffers: shared hit=179817 read=115290</font></div><div><font face="monospace"> -> Sort (cost=343618.94..344148.91 rows=211987 width=2549) (actual time=7258.314..7476.733 rows=172010 loops=3)</font></div><div><font face="monospace"> Output: column1, .. , column54</font></div><div><font face="monospace"> Sort Key: logtable.timestampcol DESC</font></div><div><font face="monospace"> Sort Method: quicksort Memory: 64730kB</font></div><div><font face="monospace"> Worker 0: Sort Method: quicksort Memory: 55742kB</font></div><div><font face="monospace"> Worker 1: Sort Method: quicksort Memory: 55565kB</font></div><div><font face="monospace"> Buffers: shared hit=179817 read=115290</font></div><div><font face="monospace"> Worker 0: actual time=7231.774..7458.703 rows=161723 loops=1</font></div><div><font face="monospace"> Buffers: shared hit=55925 read=36265</font></div><div><font face="monospace"> Worker 1: actual time=7217.856..7425.754 rows=161990 loops=1</font></div><div><font face="monospace"> Buffers: shared hit=56197 read=36242</font></div><div><font face="monospace"> -> Parallel Bitmap Heap Scan on schema.logtable (cost=5586.50..324864.86 rows=211987 width=2549) (actual time=1073.266..6805.850 rows=172010 loops=3)</font></div><div><font face="monospace"> Output: column1, .. , column54</font></div><div><font face="monospace"> Recheck Cond: ((logtable.entrytype = 4000) OR (logtable.entrytype = 4001) OR (logtable.entrytype = 4002))</font></div><div><font face="monospace"> Filter: (logtable.archivestatus <= 1)</font></div><div><font face="monospace"> Heap Blocks: exact=109146</font></div><div><font face="monospace"> Buffers: shared hit=179803 read=115290</font></div><div><font face="monospace"> Worker 0: actual time=1049.875..6809.231 rows=161723 loops=1</font></div><div><font face="monospace"> Buffers: shared hit=55918 read=36265</font></div><div><font face="monospace"> Worker 1: actual time=1035.156..6788.037 rows=161990 loops=1</font></div><div><font face="monospace"> Buffers: shared hit=56190 read=36242</font></div><div><font face="monospace"> -> BitmapOr (cost=5586.50..5586.50 rows=514483 width=0) (actual time=945.179..945.179 rows=0 loops=1)</font></div><div><font face="monospace"> Buffers: shared hit=3 read=1329</font></div><div><font face="monospace"> -> Bitmap Index Scan on idx_entrytype (cost=0.00..738.13 rows=72893 width=0) (actual time=147.915..147.916 rows=65970 loops=1)</font></div><div><font face="monospace"> Index Cond: (logtable.entrytype = 4000)</font></div><div><font face="monospace"> Buffers: shared hit=1 read=171</font></div><div><font face="monospace"> -> Bitmap Index Scan on idx_entrytype (cost=0.00..2326.17 rows=229965 width=0) (actual time=473.450..473.451 rows=225040 loops=1)</font></div><div><font face="monospace"> Index Cond: (logtable.entrytype = 4001)</font></div><div><font face="monospace"> Buffers: shared hit=1 read=579</font></div><div><font face="monospace"> -> Bitmap Index Scan on idx_entrytype (cost=0.00..2140.61 rows=211624 width=0) (actual time=323.801..323.802 rows=225021 loops=1)</font></div><div><font face="monospace"> Index Cond: (logtable.entrytype = 4002)</font></div><div><font face="monospace"> Buffers: shared hit=1 read=579</font></div><div><font face="monospace">Settings: random_page_cost = '1', search_path = '"$user", schema, public', temp_buffers = '80MB', work_mem = '1GB'</font></div><div><font face="monospace">Planning Time: 0.810 ms</font></div><div><font face="monospace">Execution Time: 9647.406 ms</font></div></blockquote><div><br />seemingly faster.<br />After doing a few selects, I reran ANALYZE:<br />Now it's even faster, probably due to cache and other mechanisms.<br /><br /></div><blockquote style="border:medium;margin:0px 0px 0px 40px;padding:0px"><div><font face="monospace">Gather Merge (cost=342639.19..391676.44 rows=420290 width=2542) (actual time=2944.803..4534.725 rows=516035 loops=1)</font></div><div><font face="monospace"> Output: column1, .. , column54</font></div><div><font face="monospace"> Workers Planned: 2</font></div><div><font face="monospace"> Workers Launched: 2</font></div><div><font face="monospace"> Buffers: shared hit=147334 read=147776</font></div><div><font face="monospace"> -> Sort (cost=341639.16..342164.53 rows=210145 width=2542) (actual time=2827.256..3013.960 rows=172012 loops=3)</font></div><div><font face="monospace"> Output: column1, .. , column54</font></div><div><font face="monospace"> Sort Key: logtable.timestampcol DESC</font></div><div><font face="monospace"> Sort Method: quicksort Memory: 71565kB</font></div><div><font face="monospace"> Worker 0: Sort Method: quicksort Memory: 52916kB</font></div><div><font face="monospace"> Worker 1: Sort Method: quicksort Memory: 51556kB</font></div><div><font face="monospace"> Buffers: shared hit=147334 read=147776</font></div><div><font face="monospace"> Worker 0: actual time=2771.975..2948.928 rows=153292 loops=1</font></div><div><font face="monospace"> Buffers: shared hit=43227 read=43808</font></div><div><font face="monospace"> Worker 1: actual time=2767.752..2938.688 rows=148424 loops=1</font></div><div><font face="monospace"> Buffers: shared hit=42246 read=42002</font></div><div><font face="monospace"> -> Parallel Bitmap Heap Scan on schema.logtable (cost=5537.95..323061.27 rows=210145 width=2542) (actual time=276.401..2418.925 rows=172012 loops=3)</font></div><div><font face="monospace"> Output: column1, .. , column54</font></div><div><font face="monospace"> Recheck Cond: ((logtable.entrytype = 4000) OR (logtable.entrytype = 4001) OR (logtable.entrytype = 4002))</font></div><div><font face="monospace"> Filter: (logtable.archivestatus <= 1)</font></div><div><font face="monospace"> Heap Blocks: exact=122495</font></div><div><font face="monospace"> Buffers: shared hit=147320 read=147776</font></div><div><font face="monospace"> Worker 0: actual time=227.701..2408.580 rows=153292 loops=1</font></div><div><font face="monospace"> Buffers: shared hit=43220 read=43808</font></div><div><font face="monospace"> Worker 1: actual time=225.996..2408.705 rows=148424 loops=1</font></div><div><font face="monospace"> Buffers: shared hit=42239 read=42002</font></div><div><font face="monospace"> -> BitmapOr (cost=5537.95..5537.95 rows=509918 width=0) (actual time=203.940..203.941 rows=0 loops=1)</font></div><div><font face="monospace"> Buffers: shared hit=1332</font></div><div><font face="monospace"> -> Bitmap Index Scan on idx_entrytype (cost=0.00..680.48 rows=67206 width=0) (actual time=31.155..31.156 rows=65970 loops=1)</font></div><div><font face="monospace"> Index Cond: (logtable.entrytype = 4000)</font></div><div><font face="monospace"> Buffers: shared hit=172</font></div><div><font face="monospace"> -> Bitmap Index Scan on idx_entrytype (cost=0.00..2220.50 rows=219476 width=0) (actual time=112.459..112.461 rows=225042 loops=1)</font></div><div><font face="monospace"> Index Cond: (logtable.entrytype = 4001)</font></div><div><font face="monospace"> Buffers: shared hit=580</font></div><div><font face="monospace"> -> Bitmap Index Scan on idx_entrytype (cost=0.00..2258.70 rows=223236 width=0) (actual time=60.313..60.314 rows=225023 loops=1)</font></div><div><font face="monospace"> Index Cond: (logtable.entrytype = 4002)</font></div><div><font face="monospace"> Buffers: shared hit=580</font></div><div><font face="monospace">Settings: random_page_cost = '1', search_path = '"$user", schema, public', temp_buffers = '80MB', work_mem = '1GB'</font></div><div><font face="monospace">Planning Time: 0.609 ms</font></div><div><font face="monospace">Execution Time: 4984.490 ms</font></div><div><br /></div></blockquote><div>I don't see the new index used but it seems it's boosting the performance nevertheless.<br />I kept the query, so I didn't rewrite the query to be WITHOUT nulls. <br />Thank you already for the hint. What else can I do? With the current parameters, the query finishes in about 3.9-5.2 seconds which is already much better but still nowhere near the speeds of 280 ms in oracle.<br />I would love to get it to at least 1 second. </div><div><br /></div></div><br /><div class="f13ca48719c8a60033905b23b39675agmail_quote"><div class="334d7d341e3233c5b27ca91297445127gmail_attr" dir="ltr">Am Do., 6. Mai 2021 um 20:20 Uhr schrieb Alexey M Boltenkov <<a href="mailto:padrebolt(at)yandex(dot)ru">padrebolt(at)yandex(dot)ru</a>>:<br /></div><blockquote class="f13ca48719c8a60033905b23b39675agmail_quote" style="border-left-color:rgb( 204 , 204 , 204 );border-left-style:solid;border-left-width:1px;margin:0px 0px 0px 0.8ex;padding-left:1ex">
<div bgcolor="#FFFFFF">
<div>On 05/06/21 21:15, Alexey M Boltenkov
wrote:<br />
</div>
<blockquote>
<div>On 05/06/21 19:11, <a href="mailto:luis(dot)roberto(at)siscobra(dot)com(dot)br" target="_blank">luis(dot)roberto(at)siscobra(dot)com(dot)br</a> wrote:<br />
</div>
<blockquote>
<pre>----- Mensagem original -----
</pre>
<blockquote>
<pre>De: "Semen Yefimenko" <a href="mailto:semen(dot)yefimenko(at)gmail(dot)com" target="_blank"><semen(dot)yefimenko(at)gmail(dot)com></a>
Para: "pgsql-performance" <a href="mailto:pgsql-performance(at)lists(dot)postgresql(dot)org" target="_blank"><pgsql-performance(at)lists(dot)postgresql(dot)org></a>
Enviadas: Quinta-feira, 6 de maio de 2021 11:38:39
Assunto: Very slow Query compared to Oracle / SQL - Server
</pre>
</blockquote>
<blockquote>
<pre>SELECT column1,..., column54 where ((entrytype = 4000 or entrytype = 4001 or
entrytype = 4002) and (archivestatus <= 1)) order by timestampcol desc;
</pre>
</blockquote>
<pre>
The first thing I would try is rewriting the query to:
SELECT column1,..., column54
FROM logtable
WHERE (entrytype in (4000,4001,4002))
AND (archivestatus <= 1))
ORDER BY timestampcol DESC;
Check if that makes a difference...
Luis R. Weck
</pre>
</blockquote>
<p><tt>The IN statement will probable result in
just recheck condition change to </tt>entrytype <tt>= any('{a,b,c}'::int[]). Looks like dispersion of
</tt>archivestatus is not enough to use index <font face="monospace">idx_arcstatus.</font></p>
<p><font face="monospace">Please try to create partial index with
condition like </font>(archivestatus <= 1) and rewrite
select to use <font face="monospace">(</font><font face="monospace">archivestatus is not
null and </font><font face="monospace">archivestatus <= 1).</font></p>
<div><font face="monospace">CREATE INDEX idx_arcstatus_le1 ON
schema.logtable ( archivestatus ) where </font><font face="monospace">(archivestatus <= 1) TABLESPACE
tablespace;<br />
<br />
</font></div>
</blockquote>
<p><tt>I'm sorry, </tt><font face="monospace">'archivestatus is not
null' is only necessary for index without nulls.<br />
</font></p>
<p><br />
<font face="monospace">CREATE INDEX idx_arcstatus_le1 ON
schema.logtable ( archivestatus ) where </font><font face="monospace">(</font><font face="monospace">archivestatus is
not null and </font><font face="monospace">archivestatus <= 1)
TABLESPACE tablespace;</font></p>
</div>
</blockquote></div>
</blockquote>
Attachment | Content-Type | Size |
---|---|---|
unknown_filename | text/html | 13.7 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Justin Pryzby | 2021-05-06 20:01:03 | Re: Very slow Query compared to Oracle / SQL - Server |
Previous Message | Vijaykumar Jain | 2021-05-06 19:48:27 | Re: Very slow Query compared to Oracle / SQL - Server |