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.
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 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.
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?