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

So, had a bit more time to look into this. Here is the issue: Your query is requesting 20 rows. However, you are doing a sort on sent_at. Because of this, the database is having to pull all rows that match the status and sender_reference, sort them, and then give you 20. From your example: 1.

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 Envoyé : lundi 12 juin 2023 22:55 À : benoit Cc : pgsql-performance@lists.postgresql.org Objet : Re: Forced to use UNION ALL when having multi

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 ('dra

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

Sadly it doesn't help to disable indexscan. The plan : https://explain.dalibo.com/plan/3b3gfce5b29c3hh4 De : Peter Geoghegan Envoyé : lundi 12 juin 2023 22:34:50 À : benoit Cc : pgsql-performance@lists.postgresql.org Objet : Re: Forced to use UNION ALL when havin

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

On Mon, Jun 12, 2023 at 1:17 PM benoit wrote: > Is there a misusage of my indexes? > > Is there a limitation when using ANY or IN operators and ordered LIMIT behind? It's complicated. Do you find that you get satisfactory performance if you force a bitmap index scan? In other words, what is the e

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

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 rep

RE: Postgresql equal join on function with columns not use index

Hi, Looks like it's the function "regexp_replace" volatile and restrict=false make the difference, we have our application role with default search_path=oracle,$user,public,pg_catalog. =#select oid,proname,pronamespace::regnamespace,prosecdef,proisstrict,provolatile from pg_p

Re: Postgresql equal join on function with columns not use index

"James Pang (chaolpan)" writes: >We migrate from Oracle to Postgresql14.8, one SQL has regression in > Postgres run in 5800 milliseconds in Postgresql v14.8, but the same SQL got > done in several hundred milliseconds in Oracle database. >With multiple table JOINs, if the join

Postgresql equal join on function with columns not use index

Hi, We migrate from Oracle to Postgresql14.8, one SQL has regression in Postgres run in 5800 milliseconds in Postgresql v14.8, but the same SQL got done in several hundred milliseconds in Oracle database. With multiple table JOINs, if the join condition is tablea.column1=tableb.