From: benoit <benoit@hopsandfork.com>
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Forced to use UNION ALL when having multiple ANY operators and ORDER BY LIMIT
Date: Mon, 12 Jun 2023 20:17:34 +0000
Message-ID: <7e0c07696f3f42189815f28c898c3133@hopsandfork.com> (raw)
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, pgsql-performance@lists.postgresql.org
Subject: Re: Forced to use UNION ALL when having multiple ANY operators and ORDER BY LIMIT
In-Reply-To: <7e0c07696f3f42189815f28c898c3133@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