pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedForced to use UNION ALL when having multiple ANY operators and ORDER BY LIMIT
8+ messages / 5 participants
[nested] [flat]
* Forced to use UNION ALL when having multiple ANY operators and ORDER BY LIMIT
@ 2023-06-12 20:17 benoit <benoit@hopsandfork.com>
0 siblings, 2 replies; 8+ messages in thread
From: benoit @ 2023-06-12 20:17 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Hello
I have a database with few 60gb tables. Tables rows are requested with multiple ANY or IN operators. I am not able to find an easy way to make DB able to use indexes. I often hit the index, but see a a spike of 200mb of IO or disk read.
I am using version 13 but soon 14.
I wrote a reproduction script on version 14 with plans included. https://gist.github.com/benoittgt/ab72dc4cfedea2a0c6a5ee809d16e04d
I also have plans on a snapshot of the DB with real data.
- The current query that I try to improve : https://explain.dalibo.com/plan/8b8f6e0he9feb551
- I added the DB schema + index in query view. As you can see I have many indexes for testing purpose and try what the planner can do.
- The optimized query when I have only one ANY and migrate to UNION ALL for each parameter of the ANY operator https://explain.dalibo.com/plan/427gg053d07328ga . Query is fast as I would like but it means generate some merge to be able to get a fast result.
- The new issue I have when I have a new ANY operator on the previous optimized query. Big IO/read https://explain.dalibo.com/plan/e7ha9g637b4eh946
It seems to me quite undoable to generate for every parameters a query that will then merge. I have sometimes 3-4 ANY operators with up to 15 elements in an array.
Is there a misusage of my indexes?
Is there a limitation when using ANY or IN operators and ordered LIMIT behind?
Thanks a lot
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Forced to use UNION ALL when having multiple ANY operators and ORDER BY LIMIT
@ 2023-06-12 20:34 Peter Geoghegan <pg@bowt.ie>
parent: benoit <benoit@hopsandfork.com>
1 sibling, 1 reply; 8+ messages in thread
From: Peter Geoghegan @ 2023-06-12 20:34 UTC (permalink / raw)
To: benoit <benoit@hopsandfork.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
On Mon, Jun 12, 2023 at 1:17 PM benoit <benoit@hopsandfork.com> wrote:
> Is there a misusage of my indexes?
>
> Is there a limitation when using ANY or IN operators and ordered LIMIT behind?
It's complicated. Do you find that you get satisfactory performance if
you force a bitmap index scan? In other words, what is the effect of
"set enable_indexscan = off" on your original query? Does that speed
up execution at all? (I think that this approach ought to produce a
plan that uses a bitmap index scan in place of the index scan, without
changing anything else.)
--
Peter Geoghegan
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: Forced to use UNION ALL when having multiple ANY operators and ORDER BY LIMIT
@ 2023-06-12 20:50 benoit <benoit@hopsandfork.com>
parent: Peter Geoghegan <pg@bowt.ie>
0 siblings, 0 replies; 8+ messages in thread
From: benoit @ 2023-06-12 20:50 UTC (permalink / raw)
To: Peter Geoghegan <pg@bowt.ie>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Sadly it doesn't help to disable indexscan. The plan : https://explain.dalibo.com/plan/3b3gfce5b29c3hh4
________________________________
De : Peter Geoghegan <pg@bowt.ie>
Envoyé : lundi 12 juin 2023 22:34:50
À : benoit
Cc : pgsql-performance@lists.postgresql.org
Objet : Re: Forced to use UNION ALL when having multiple ANY operators and ORDER BY LIMIT
On Mon, Jun 12, 2023 at 1:17 PM benoit <benoit@hopsandfork.com> wrote:
> Is there a misusage of my indexes?
>
> Is there a limitation when using ANY or IN operators and ordered LIMIT behind?
It's complicated. Do you find that you get satisfactory performance if
you force a bitmap index scan? In other words, what is the effect of
"set enable_indexscan = off" on your original query? Does that speed
up execution at all? (I think that this approach ought to produce a
plan that uses a bitmap index scan in place of the index scan, without
changing anything else.)
--
Peter Geoghegan
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Forced to use UNION ALL when having multiple ANY operators and ORDER BY LIMIT
@ 2023-06-12 20:55 Chris Hoover <chrish@aweber.com>
parent: benoit <benoit@hopsandfork.com>
1 sibling, 1 reply; 8+ messages in thread
From: Chris Hoover @ 2023-06-12 20:55 UTC (permalink / raw)
To: benoit <benoit@hopsandfork.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
I normally create my indexes to match the where clause of the query. While technically, it should not matter, I find a lot of time, it does.
I would create an index on (status, sender_reference, sent_at) and see if the improves your query performance.
SELECT *
FROM docs
WHERE status IN ('draft', 'sent')
AND sender_reference IN ('Custom/1175', 'Client/362', 'Custom/280')
ORDER BY sent_at DESC
Thanks,
Chris Hoover
Senior DBA
AWeber.com
Cell: (803) 528-2269
Email: chrish@aweber.com
> On Jun 12, 2023, at 4:17 PM, benoit <benoit@hopsandfork.com> wrote:
>
> Hello
>
> I have a database with few 60gb tables. Tables rows are requested with multiple ANY or IN operators. I am not able to find an easy way to make DB able to use indexes. I often hit the index, but see a a spike of 200mb of IO or disk read.
>
> I am using version 13 but soon 14.
>
> I wrote a reproduction script on version 14 with plans included. https://gist.github.com/benoittgt/ab72dc4cfedea2a0c6a5ee809d16e04d
>
> I also have plans on a snapshot of the DB with real data.
> - The current query that I try to improve : https://explain.dalibo.com/plan/8b8f6e0he9feb551
> - I added the DB schema + index in query view. As you can see I have many indexes for testing purpose and try what the planner can do.
> - The optimized query when I have only one ANY and migrate to UNION ALL for each parameter of the ANY operator https://explain.dalibo.com/plan/427gg053d07328ga . Query is fast as I would like but it means generate some merge to be able to get a fast result.
> - The new issue I have when I have a new ANY operator on the previous optimized query. Big IO/read https://explain.dalibo.com/plan/e7ha9g637b4eh946
>
> It seems to me quite undoable to generate for every parameters a query that will then merge. I have sometimes 3-4 ANY operators with up to 15 elements in an array.
>
> Is there a misusage of my indexes?
> Is there a limitation when using ANY or IN operators and ordered LIMIT behind?
>
> Thanks a lot
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: Forced to use UNION ALL when having multiple ANY operators and ORDER BY LIMIT
@ 2023-06-12 21:34 benoit <benoit@hopsandfork.com>
parent: Chris Hoover <chrish@aweber.com>
0 siblings, 2 replies; 8+ messages in thread
From: benoit @ 2023-06-12 21:34 UTC (permalink / raw)
To: Chris Hoover <chrish@aweber.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
This new index is used but still the read is 230mb.
https://explain.dalibo.com/plan/b0f28a9e8a136afd
________________________________
De : Chris Hoover <chrish@aweber.com>
Envoyé : lundi 12 juin 2023 22:55
À : benoit
Cc : pgsql-performance@lists.postgresql.org
Objet : Re: Forced to use UNION ALL when having multiple ANY operators and ORDER BY LIMIT
I normally create my indexes to match the where clause of the query. While technically, it should not matter, I find a lot of time, it does.
I would create an index on (status, sender_reference, sent_at) and see if the improves your query performance.
SELECT * FROM docs WHERE status IN ('draft', 'sent') AND sender_reference IN ('Custom/1175', 'Client/362', 'Custom/280') ORDER BY sent_at DESC
Thanks,
Chris Hoover
Senior DBA
AWeber.com
Cell: (803) 528-2269
Email: chrish@aweber.com
On Jun 12, 2023, at 4:17 PM, benoit <benoit@hopsandfork.com> wrote:
Hello
I have a database with few 60gb tables. Tables rows are requested with multiple ANY or IN operators. I am not able to find an easy way to make DB able to use indexes. I often hit the index, but see a a spike of 200mb of IO or disk read.
I am using version 13 but soon 14.
I wrote a reproduction script on version 14 with plans included. https://gist.github.com/benoittgt/ab72dc4cfedea2a0c6a5ee809d16e04d
I also have plans on a snapshot of the DB with real data.
- The current query that I try to improve : https://explain.dalibo.com/plan/8b8f6e0he9feb551
- I added the DB schema + index in query view. As you can see I have many indexes for testing purpose and try what the planner can do.
- The optimized query when I have only one ANY and migrate to UNION ALL for each parameter of the ANY operator https://explain.dalibo.com/plan/427gg053d07328ga . Query is fast as I would like but it means generate some merge to be able to get a fast result.
- The new issue I have when I have a new ANY operator on the previous optimized query. Big IO/read https://explain.dalibo.com/plan/e7ha9g637b4eh946
It seems to me quite undoable to generate for every parameters a query that will then merge. I have sometimes 3-4 ANY operators with up to 15 elements in an array.
Is there a misusage of my indexes?
Is there a limitation when using ANY or IN operators and ordered LIMIT behind?
Thanks a lot
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Forced to use UNION ALL when having multiple ANY operators and ORDER BY LIMIT
@ 2023-06-13 00:09 Chris Hoover <chrish@aweber.com>
parent: benoit <benoit@hopsandfork.com>
1 sibling, 0 replies; 8+ messages in thread
From: Chris Hoover @ 2023-06-13 00:09 UTC (permalink / raw)
To: benoit <benoit@hopsandfork.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
So, had a bit more time to look into this.
Here is the issue:
Your query is requesting 20 rows. However, you are doing a sort on sent_at. Because of this, the database is having to pull all rows that match the status and sender_reference, sort them, and then give you 20.
From your example:
1. You have 29744 rows that match your criteria. ->
Index Scan using docs_sent_at_idx_1 on public.docs (cost=0.56..39748945.58 rows=29744 width=38)
2. To get those 29744 rows, the database had to read 5046 database blocks (8KB/block). 45046 * 8KB is your ~220MB of read. ->
Buffers: shared hit=1421 read=45046
3. Since you are selecting all rows, the database uses the index to find the matching rows and then has to go read the head blocks to retrieve and validate the rows. Then it sorts all the returned rows by date and returns
the first 20 rows.
So, PostgreSQL is preforming well. It’s just an expensive way to get 20 rows and I don’t see an easy way to make it better if that is what is needed.
Thanks,
Chris Hoover
Senior DBA
AWeber.com
Cell: (803) 528-2269
Email: chrish@aweber.com
> On Jun 12, 2023, at 5:34 PM, benoit <benoit@hopsandfork.com> wrote:
>
> This new index is used but still the read is 230mb.
>
> https://explain.dalibo.com/plan/b0f28a9e8a136afd
>
>
> De : Chris Hoover <chrish@aweber.com>
> Envoyé : lundi 12 juin 2023 22:55
> À : benoit
> Cc : pgsql-performance@lists.postgresql.org
> Objet : Re: Forced to use UNION ALL when having multiple ANY operators and ORDER BY LIMIT
>
> I normally create my indexes to match the where clause of the query. While technically, it should not matter, I find a lot of time, it does.
>
> I would create an index on (status, sender_reference, sent_at) and see if the improves your query performance.
>
>
> SELECT * FROM docs WHERE status
> IN ('draft',
> 'sent')
> AND sender_reference
> IN ('Custom/1175',
> 'Client/362',
> 'Custom/280')
> ORDER BY sent_at DESC
> Thanks,
>
>
> Chris Hoover
> Senior DBA
> AWeber.com
> Cell: (803) 528-2269
> Email: chrish@aweber.com
>
>
>
>> On Jun 12, 2023, at 4:17 PM, benoit <benoit@hopsandfork.com> wrote:
>>
>> Hello
>>
>> I have a database with few 60gb tables. Tables rows are requested with multiple ANY or IN operators. I am not able to find an easy way to make DB able to use indexes. I often hit the index, but see a a spike of 200mb of IO or disk read.
>>
>> I am using version 13 but soon 14.
>>
>> I wrote a reproduction script on version 14 with plans included. https://gist.github.com/benoittgt/ab72dc4cfedea2a0c6a5ee809d16e04d
>>
>> I also have plans on a snapshot of the DB with real data.
>> - The current query that I try to improve : https://explain.dalibo.com/plan/8b8f6e0he9feb551
>> - I added the DB schema + index in query view. As you can see I have many indexes for testing purpose and try what the planner can do.
>> - The optimized query when I have only one ANY and migrate to UNION ALL for each parameter of the ANY operator https://explain.dalibo.com/plan/427gg053d07328ga . Query is fast as I would like but it means generate some merge to be able to get a fast result.
>> - The new issue I have when I have a new ANY operator on the previous optimized query. Big IO/read https://explain.dalibo.com/plan/e7ha9g637b4eh946
>>
>> It seems to me quite undoable to generate for every parameters a query that will then merge. I have sometimes 3-4 ANY operators with up to 15 elements in an array.
>>
>> Is there a misusage of my indexes?
>> Is there a limitation when using ANY or IN operators and ordered LIMIT behind?
>>
>> Thanks a lot
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: Forced to use UNION ALL when having multiple ANY operators and ORDER BY LIMIT
@ 2023-06-18 21:23 msalais@msym.fr
parent: benoit <benoit@hopsandfork.com>
1 sibling, 1 reply; 8+ messages in thread
From: msalais@msym.fr @ 2023-06-18 21:23 UTC (permalink / raw)
To: 'benoit' <benoit@hopsandfork.com>; 'Chris Hoover' <chrish@aweber.com>; +Cc: pgsql-performance@lists.postgresql.org
Hi,
Do you really need to do select *?
In other words, is it necessary to have all columns in the result?
Michel SALAIS
De : benoit <benoit@hopsandfork.com>
Envoyé : lundi 12 juin 2023 23:35
À : Chris Hoover <chrish@aweber.com>
Cc : pgsql-performance@lists.postgresql.org
Objet : RE: Forced to use UNION ALL when having multiple ANY operators and
ORDER BY LIMIT
This new index is used but still the read is 230mb.
https://explain.dalibo.com/plan/b0f28a9e8a136afd
_____
De : Chris Hoover <chrish@aweber.com <mailto:chrish@aweber.com> >
Envoyé : lundi 12 juin 2023 22:55
À : benoit
Cc : pgsql-performance@lists.postgresql.org
<mailto:pgsql-performance@lists.postgresql.org>
Objet : Re: Forced to use UNION ALL when having multiple ANY operators and
ORDER BY LIMIT
I normally create my indexes to match the where clause of the query. While
technically, it should not matter, I find a lot of time, it does.
I would create an index on (status, sender_reference, sent_at) and see if
the improves your query performance.
SELECT * FROM docs WHERE status
IN ('draft',
'sent')
AND sender_reference
IN ('Custom/1175',
'Client/362',
'Custom/280')
ORDER BY sent_at DESC
Thanks,
Chris Hoover
Senior DBA
AWeber.com
Cell: (803) 528-2269
Email: chrish@aweber.com <mailto:chrish@aweber.com>
On Jun 12, 2023, at 4:17 PM, benoit <benoit@hopsandfork.com
<mailto:benoit@hopsandfork.com> > wrote:
Hello
I have a database with few 60gb tables. Tables rows are requested with
multiple ANY or IN operators. I am not able to find an easy way to make DB
able to use indexes. I often hit the index, but see a a spike of 200mb of IO
or disk read.
I am using version 13 but soon 14.
I wrote a reproduction script on version 14 with plans included.
https://gist.github.com/benoittgt/ab72dc4cfedea2a0c6a5ee809d16e04d
I also have plans on a snapshot of the DB with real data.
- The current query that I try to improve :
https://explain.dalibo.com/plan/8b8f6e0he9feb551
- I added the DB schema + index in query view. As you can see I have many
indexes for testing purpose and try what the planner can do.
- The optimized query when I have only one ANY and migrate to UNION ALL for
each parameter of the ANY operator
https://explain.dalibo.com/plan/427gg053d07328ga . Query is fast as I would
like but it means generate some merge to be able to get a fast result.
- The new issue I have when I have a new ANY operator on the previous
optimized query. Big IO/read
https://explain.dalibo.com/plan/e7ha9g637b4eh946
It seems to me quite undoable to generate for every parameters a query that
will then merge. I have sometimes 3-4 ANY operators with up to 15 elements
in an array.
Is there a misusage of my indexes?
Is there a limitation when using ANY or IN operators and ordered LIMIT
behind?
Thanks a lot
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Forced to use UNION ALL when having multiple ANY operators and ORDER BY LIMIT
@ 2023-06-19 16:30 Benoit Tigeot <benoit@hopsandfork.com>
parent: msalais@msym.fr
0 siblings, 0 replies; 8+ messages in thread
From: Benoit Tigeot @ 2023-06-19 16:30 UTC (permalink / raw)
To: msalais@msym.fr; Chris Hoover <chrish@aweber.com>; +Cc: pgsql-performance@lists.postgresql.org
No it is not. But do you think there is an impact here?
Le 18/06/2023 à 23:23, msalais@msym.fr a écrit :
>
> Hi,
>
> Do you really need to do “select *”?
>
> In other words, is it necessary to have all columns in the result?
>
> /Michel SALAIS/
>
> *De :*benoit <benoit@hopsandfork.com>
> *Envoyé :* lundi 12 juin 2023 23:35
> *À :* Chris Hoover <chrish@aweber.com>
> *Cc :* pgsql-performance@lists.postgresql.org
> *Objet :* RE: Forced to use UNION ALL when having multiple ANY
> operators and ORDER BY LIMIT
>
> This new index is used but still the read is 230mb.
>
> https://explain.dalibo.com/plan/b0f28a9e8a136afd
>
> ------------------------------------------------------------------------
>
> *De :*Chris Hoover <chrish@aweber.com>
> *Envoyé :* lundi 12 juin 2023 22:55
> *À :* benoit
> *Cc :* pgsql-performance@lists.postgresql.org
> *Objet :* Re: Forced to use UNION ALL when having multiple ANY
> operators and ORDER BY LIMIT
>
> I normally create my indexes to match the where clause of the query.
> While technically, it should not matter, I find a lot of time, it does.
>
> I would create an index on (status, sender_reference, sent_at) and see
> if the improves your query performance.
>
>
>
>
> SELECT * FROM docs WHEREstatus
>
> IN('draft',
>
> 'sent')
>
> ANDsender_reference
>
> IN('Custom/1175',
>
> 'Client/362',
>
> 'Custom/280')
>
> ORDER BYsent_at DESC
>
> Thanks,
>
> Chris Hoover
>
> Senior DBA
>
> AWeber.com
>
> Cell: (803) 528-2269
>
> Email: chrish@aweber.com
>
>
>
> On Jun 12, 2023, at 4:17 PM, benoit <benoit@hopsandfork.com> wrote:
>
> Hello
>
> I have a database with few 60gb tables. Tables rows are requested
> with multiple ANY or IN operators. I am not able to find an easy
> way to make DB able to use indexes. I often hit the index, but see
> a a spike of 200mb of IO or disk read.
>
> I am using version 13 but soon 14.
>
> I wrote a reproduction script on version 14 with plans included.
> https://gist.github.com/benoittgt/ab72dc4cfedea2a0c6a5ee809d16e04d
>
> I also have plans on a snapshot of the DB with real data.
>
> - The current query that I try to improve :
> https://explain.dalibo.com/plan/8b8f6e0he9feb551
>
> - I added the DB schema + index in query view. As you can see I
> have many indexes for testing purpose and try what the planner can do.
>
> - The optimized query when I have only one ANY and migrate to
> UNION ALL for each parameter of the ANY operator
> https://explain.dalibo.com/plan/427gg053d07328ga . Query is fast
> as I would like but it means generate some merge to be able to get
> a fast result.
>
> - The new issue I have when I have a new ANY operator on the
> previous optimized query. Big IO/read
> https://explain.dalibo.com/plan/e7ha9g637b4eh946
>
> It seems to me quite undoable to generate for every parameters a
> query that will then merge. I have sometimes 3-4 ANY operators
> with up to 15 elements in an array.
>
> Is there a misusage of my indexes?
>
> Is there a limitation when using ANY or IN operators and
> ordered LIMIT behind?
>
> Thanks a lot
>
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2023-06-19 16:30 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-06-12 20:17 Forced to use UNION ALL when having multiple ANY operators and ORDER BY LIMIT benoit <benoit@hopsandfork.com>
2023-06-12 20:34 ` Peter Geoghegan <pg@bowt.ie>
2023-06-12 20:50 ` benoit <benoit@hopsandfork.com>
2023-06-12 20:55 ` Chris Hoover <chrish@aweber.com>
2023-06-12 21:34 ` benoit <benoit@hopsandfork.com>
2023-06-13 00:09 ` Chris Hoover <chrish@aweber.com>
2023-06-18 21:23 ` msalais@msym.fr
2023-06-19 16:30 ` Benoit Tigeot <benoit@hopsandfork.com>
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