Re: Forced to use UNION ALL when having multiple ANY operators and ORDER BY LIMIT

No it is not. But do you think there is an impact here?

Le 18/06/2023 à 23:23, msalais(at)msym(dot)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(at)hopsandfork(dot)com>
> *Envoyé :* lundi 12 juin 2023 23:35
> *À :* Chris Hoover <chrish(at)aweber(dot)com>
> *Cc :* pgsql-performance(at)lists(dot)postgresql(dot)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(at)aweber(dot)com>
> *Envoyé :* lundi 12 juin 2023 22:55
> *À :* benoit
> *Cc :* pgsql-performance(at)lists(dot)postgresql(dot)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(at)aweber(dot)com
>
>
>
> On Jun 12, 2023, at 4:17 PM, benoit <benoit(at)hopsandfork(dot)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
>