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 <ben...@hopsandfork.com> Envoyé : lundi 12 juin 2023 23:35 À : Chris Hoover <chr...@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 <chr...@aweber.com <mailto:chr...@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: chr...@aweber.com <mailto:chr...@aweber.com> On Jun 12, 2023, at 4:17 PM, benoit <ben...@hopsandfork.com <mailto:ben...@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
