From: Benoit Tigeot <benoit@hopsandfork.com>
To: msalais@msym.fr
To: Chris Hoover <chrish@aweber.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Forced to use UNION ALL when having multiple ANY operators and ORDER BY LIMIT
Date: Mon, 19 Jun 2023 18:30:12 +0200
Message-ID: <8a143743-e850-693b-cc03-7f58eb33b646@hopsandfork.com> (raw)
In-Reply-To: <005301d9a22b$28841ae0$798c50a0$@msym.fr>
References: <7e0c07696f3f42189815f28c898c3133@hopsandfork.com>
<229CB377-96AD-493F-A454-A70F7915B69F@aweber.com>
<847f1886d2f94d619ebbd3d1394b74f1@hopsandfork.com>
<005301d9a22b$28841ae0$798c50a0$@msym.fr>
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
>
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: benoit@hopsandfork.com, msalais@msym.fr, chrish@aweber.com, pgsql-performance@lists.postgresql.org
Subject: Re: Forced to use UNION ALL when having multiple ANY operators and ORDER BY LIMIT
In-Reply-To: <8a143743-e850-693b-cc03-7f58eb33b646@hopsandfork.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