pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedBad query plan when you add many OR conditions
11+ messages / 6 participants
[nested] [flat]
* Bad query plan when you add many OR conditions
@ 2020-01-10 01:11 Marco Colli <collimarco91@gmail.com>
2020-01-10 03:06 ` Re: Bad query plan when you add many OR conditions Justin Pryzby <pryzby@telsasoft.com>
2020-01-10 17:12 ` Re: Bad query plan when you add many OR conditions Jeff Janes <jeff.janes@gmail.com>
2020-01-14 08:01 ` Re: Bad query plan when you add many OR conditions Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 3 replies; 11+ messages in thread
From: Marco Colli @ 2020-01-10 01:11 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Hello!
I have a query on a large table that is very fast (0s):
https://gist.github.com/collimarco/039412b4fe0dcf39955888f96eff29db#file-fast_query-txt
Basically the query matches the rows that have a tag1 OR tag2 OR tag3 OR
tag4 OR tag5...
However if you increase the number of OR at some point PostgreSQL makes the
bad decision to change its query plan! And the new plan makes the query
terribly slow:
https://gist.github.com/collimarco/039412b4fe0dcf39955888f96eff29db#file-slow_query-txt
Instead of this (which is fast):
Bitmap Index Scan on index_subscriptions_on_project_id_and_tags
It starts using this (which is slow):
Parallel Index Scan using index_subscriptions_on_project_id_and_created_at
The choice seems quite stupid since it doesn't have the tags on the new
index... and indeed the query takes about 1 minute instead of a few
milliseconds. Here's a list of the available indexes:
https://gist.github.com/collimarco/039412b4fe0dcf39955888f96eff29db#file-_indexes-txt
How can I encourage PostgreSQL to use the Bitmap Index Scan even when there
are many OR conditions? I have tried with VACUUM ANALYZE subscriptions but
it doesn't help.
Note: the query is generated dynamically by customers of a SaaS, so I don't
have full control on it
Thank you very much for any advice!
Marco Colli
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Bad query plan when you add many OR conditions
2020-01-10 01:11 Bad query plan when you add many OR conditions Marco Colli <collimarco91@gmail.com>
@ 2020-01-10 03:06 ` Justin Pryzby <pryzby@telsasoft.com>
2020-01-10 11:03 ` Re: Bad query plan when you add many OR conditions Marco Colli <collimarco91@gmail.com>
2020-01-10 13:30 ` Re: Bad query plan when you add many OR conditions Marco Colli <collimarco91@gmail.com>
2 siblings, 2 replies; 11+ messages in thread
From: Justin Pryzby @ 2020-01-10 03:06 UTC (permalink / raw)
To: Marco Colli <collimarco91@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On Fri, Jan 10, 2020 at 02:11:14AM +0100, Marco Colli wrote:
> I have a query on a large table that is very fast (0s):
> https://gist.github.com/collimarco/039412b4fe0dcf39955888f96eff29db#file-fast_query-txt
ORDER BY + LIMIT is a query which sometimes has issues, you can probably find
more by searching. The planner thinks it'll hit the LIMIT pretty soon and only
run a fraction of the index scan - but then it turns out to be wrong.
You might have poor statistics on project_id and/or tags. This *might* help:
ALTER TABLE subscriptions ALTER project_id SET STATISTICS 2000; ANALYZE subscriptions;
But I'm guessing there's correlation between the two, which the planner doesn't
know. If you're running at least v10, I'm guessing it would help to CREATE
STATISTICS on those columns (and analyze).
See one similar problem here (not involving LIMIT).
https://www.postgresql.org/message-id/flat/CABFxtPedz4zL%2BaPWut4%2B%3Dum4av1aAXr6OVRfRB_6K7mJKMbEcw...
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Bad query plan when you add many OR conditions
2020-01-10 01:11 Bad query plan when you add many OR conditions Marco Colli <collimarco91@gmail.com>
2020-01-10 03:06 ` Re: Bad query plan when you add many OR conditions Justin Pryzby <pryzby@telsasoft.com>
@ 2020-01-10 11:03 ` Marco Colli <collimarco91@gmail.com>
2020-01-10 13:34 ` Re: Bad query plan when you add many OR conditions Justin Pryzby <pryzby@telsasoft.com>
1 sibling, 1 reply; 11+ messages in thread
From: Marco Colli @ 2020-01-10 11:03 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
I am trying different solutions and what I have found is even more
surprising to me...
The query is always this:
https://gist.github.com/collimarco/039412b4fe0dcf39955888f96eff29db#file-slow_query-txt
I have added this index which would allow an index only scan:
"index_subscriptions_on_project_id_and_created_at_and_tags" btree
(project_id, created_at DESC, tags) WHERE trashed_at IS NULL
But Postgresql continues to use this index (which has less information and
then requires slow access to disk):
"index_subscriptions_on_project_id_and_created_at" btree (project_id,
created_at DESC)
On Fri, Jan 10, 2020 at 4:06 AM Justin Pryzby <pryzby@telsasoft.com> wrote:
> On Fri, Jan 10, 2020 at 02:11:14AM +0100, Marco Colli wrote:
> > I have a query on a large table that is very fast (0s):
> >
> https://gist.github.com/collimarco/039412b4fe0dcf39955888f96eff29db#file-fast_query-txt
>
> ORDER BY + LIMIT is a query which sometimes has issues, you can probably
> find
> more by searching. The planner thinks it'll hit the LIMIT pretty soon and
> only
> run a fraction of the index scan - but then it turns out to be wrong.
>
> You might have poor statistics on project_id and/or tags. This *might*
> help:
> ALTER TABLE subscriptions ALTER project_id SET STATISTICS 2000; ANALYZE
> subscriptions;
>
> But I'm guessing there's correlation between the two, which the planner
> doesn't
> know. If you're running at least v10, I'm guessing it would help to CREATE
> STATISTICS on those columns (and analyze).
>
> See one similar problem here (not involving LIMIT).
>
> https://www.postgresql.org/message-id/flat/CABFxtPedz4zL%2BaPWut4%2B%3Dum4av1aAXr6OVRfRB_6K7mJKMbEcw...
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Bad query plan when you add many OR conditions
2020-01-10 01:11 Bad query plan when you add many OR conditions Marco Colli <collimarco91@gmail.com>
2020-01-10 03:06 ` Re: Bad query plan when you add many OR conditions Justin Pryzby <pryzby@telsasoft.com>
2020-01-10 11:03 ` Re: Bad query plan when you add many OR conditions Marco Colli <collimarco91@gmail.com>
@ 2020-01-10 13:34 ` Justin Pryzby <pryzby@telsasoft.com>
2020-01-10 14:53 ` Re: Bad query plan when you add many OR conditions Marco Colli <collimarco91@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Justin Pryzby @ 2020-01-10 13:34 UTC (permalink / raw)
To: Marco Colli <collimarco91@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On Fri, Jan 10, 2020 at 12:03:39PM +0100, Marco Colli wrote:
> I have added this index which would allow an index only scan:
> "index_subscriptions_on_project_id_and_created_at_and_tags" btree
> (project_id, created_at DESC, tags) WHERE trashed_at IS NULL
Are those the only columns in subscriptions ?
> But Postgresql continues to use this index (which has less information and
> then requires slow access to disk):
> "index_subscriptions_on_project_id_and_created_at" btree (project_id,
> created_at DESC)
Did you vacuum the table ?
Did you try to "explain" the query after dropping the 1st index (like: begin;
DROP INDEX..; explain analyze..; rollback).
Also, is the first (other) index btree_gin (you can \dx to show extensions) ?
I think it needs to be a gin index to search tags ?
On Fri, Jan 10, 2020 at 01:42:24PM +0100, Marco Colli wrote:
> I would like to try your solution but I read that ALTER TABLE... SET
> STATISTICS locks the table... Since it is just an experiment and we don't
> know if it actually works it would be greate to avoid locking a large table
> (50M) in production.
I suggest to CREATE TABLE test_subscriptions (LIKE subscriptions INCLUDING
ALL); INSERT INTO test_subscriptions SELECT * FROM subscriptions; ANALYZE test_subscriptions;
Anyway, ALTER..SET STATS requires a strong lock but for only a brief moment
(assuming it doesn't have to wait). Possibly you'd be ok doing SET
statement_timeout='1s'; ALTER TABLE....
> Does CREATE STATISTICS lock the table too?
You can check by SET client_min_messages=debug; SET lock_timeout=333; SET log_lock_waits=on;
Looks like it needs ShareUpdateExclusiveLock.
> Does statistics work on an array field like tags? (I can't find any
> information)
It think it'd be data type agnostic. And seems to work with arrays.
On Fri, Jan 10, 2020 at 02:30:27PM +0100, Marco Colli wrote:
> @Justin Pryzby I have tried this as you suggested:
>
> CREATE STATISTICS statistics_on_subscriptions_project_id_and_tags ON
> project_id, tags FROM subscriptions;
> VACUUM ANALYZE subscriptions;
>
> Unfortunately nothing changes and Postgresql continues to use the wrong
> plan (maybe stats don't work well on array fields like tags??).
It'd help to see SELECT stxddependencies FROM pg_statistic_ext WHERE
stxoid='subscriptions'::regclass
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Bad query plan when you add many OR conditions
2020-01-10 01:11 Bad query plan when you add many OR conditions Marco Colli <collimarco91@gmail.com>
2020-01-10 03:06 ` Re: Bad query plan when you add many OR conditions Justin Pryzby <pryzby@telsasoft.com>
2020-01-10 11:03 ` Re: Bad query plan when you add many OR conditions Marco Colli <collimarco91@gmail.com>
2020-01-10 13:34 ` Re: Bad query plan when you add many OR conditions Justin Pryzby <pryzby@telsasoft.com>
@ 2020-01-10 14:53 ` Marco Colli <collimarco91@gmail.com>
2020-01-10 15:18 ` Re: Bad query plan when you add many OR conditions Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: Marco Colli @ 2020-01-10 14:53 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; pgsql-performance@lists.postgresql.org
Before trying other solutions I would like to make PG use an index-only
scan (it should be fast enough for our purpose).
I have tried to disable the other indexes and forced PG to use this index
(which includes all the fields of the query):
index_subscriptions_on_project_id_and_created_at_and_tags
The problem is that the query plan is this:
https://gist.github.com/collimarco/03f3dde372f001485518b8deca2f3b24#file-index_scan_instead_of_index...
As you can see it is a *index scan* and not an *index only* scan... I don't
understand why. The index includes all the fields used by the query... so
an index only scan should be possible.
On Fri, Jan 10, 2020 at 2:34 PM Justin Pryzby <pryzby@telsasoft.com> wrote:
> On Fri, Jan 10, 2020 at 12:03:39PM +0100, Marco Colli wrote:
> > I have added this index which would allow an index only scan:
> > "index_subscriptions_on_project_id_and_created_at_and_tags" btree
> > (project_id, created_at DESC, tags) WHERE trashed_at IS NULL
>
> Are those the only columns in subscriptions ?
>
> > But Postgresql continues to use this index (which has less information
> and
> > then requires slow access to disk):
> > "index_subscriptions_on_project_id_and_created_at" btree (project_id,
> > created_at DESC)
>
> Did you vacuum the table ?
> Did you try to "explain" the query after dropping the 1st index (like:
> begin;
> DROP INDEX..; explain analyze..; rollback).
>
> Also, is the first (other) index btree_gin (you can \dx to show
> extensions) ?
>
> I think it needs to be a gin index to search tags ?
>
> On Fri, Jan 10, 2020 at 01:42:24PM +0100, Marco Colli wrote:
> > I would like to try your solution but I read that ALTER TABLE... SET
> > STATISTICS locks the table... Since it is just an experiment and we
> don't
> > know if it actually works it would be greate to avoid locking a large
> table
> > (50M) in production.
>
> I suggest to CREATE TABLE test_subscriptions (LIKE subscriptions INCLUDING
> ALL); INSERT INTO test_subscriptions SELECT * FROM subscriptions; ANALYZE
> test_subscriptions;
>
> Anyway, ALTER..SET STATS requires a strong lock but for only a brief moment
> (assuming it doesn't have to wait). Possibly you'd be ok doing SET
> statement_timeout='1s'; ALTER TABLE....
>
> > Does CREATE STATISTICS lock the table too?
>
> You can check by SET client_min_messages=debug; SET lock_timeout=333; SET
> log_lock_waits=on;
> Looks like it needs ShareUpdateExclusiveLock.
>
> > Does statistics work on an array field like tags? (I can't find any
> > information)
>
> It think it'd be data type agnostic. And seems to work with arrays.
>
> On Fri, Jan 10, 2020 at 02:30:27PM +0100, Marco Colli wrote:
> > @Justin Pryzby I have tried this as you suggested:
> >
> > CREATE STATISTICS statistics_on_subscriptions_project_id_and_tags ON
> > project_id, tags FROM subscriptions;
> > VACUUM ANALYZE subscriptions;
> >
> > Unfortunately nothing changes and Postgresql continues to use the wrong
> > plan (maybe stats don't work well on array fields like tags??).
>
> It'd help to see SELECT stxddependencies FROM pg_statistic_ext WHERE
> stxoid='subscriptions'::regclass
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Bad query plan when you add many OR conditions
2020-01-10 01:11 Bad query plan when you add many OR conditions Marco Colli <collimarco91@gmail.com>
2020-01-10 03:06 ` Re: Bad query plan when you add many OR conditions Justin Pryzby <pryzby@telsasoft.com>
2020-01-10 11:03 ` Re: Bad query plan when you add many OR conditions Marco Colli <collimarco91@gmail.com>
2020-01-10 13:34 ` Re: Bad query plan when you add many OR conditions Justin Pryzby <pryzby@telsasoft.com>
2020-01-10 14:53 ` Re: Bad query plan when you add many OR conditions Marco Colli <collimarco91@gmail.com>
@ 2020-01-10 15:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-01-10 16:03 ` Re: Bad query plan when you add many OR conditions Marco Colli <collimarco91@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2020-01-10 15:18 UTC (permalink / raw)
To: Marco Colli <collimarco91@gmail.com>; +Cc: Justin Pryzby <pryzby@telsasoft.com>; pgsql-performance@lists.postgresql.org
Marco Colli <collimarco91@gmail.com> writes:
> As you can see it is a *index scan* and not an *index only* scan... I don't
> understand why. The index includes all the fields used by the query... so
> an index only scan should be possible.
Huh? The query is "select * from ...", so it retrieves *all* columns
of the table.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Bad query plan when you add many OR conditions
2020-01-10 01:11 Bad query plan when you add many OR conditions Marco Colli <collimarco91@gmail.com>
2020-01-10 03:06 ` Re: Bad query plan when you add many OR conditions Justin Pryzby <pryzby@telsasoft.com>
2020-01-10 11:03 ` Re: Bad query plan when you add many OR conditions Marco Colli <collimarco91@gmail.com>
2020-01-10 13:34 ` Re: Bad query plan when you add many OR conditions Justin Pryzby <pryzby@telsasoft.com>
2020-01-10 14:53 ` Re: Bad query plan when you add many OR conditions Marco Colli <collimarco91@gmail.com>
2020-01-10 15:18 ` Re: Bad query plan when you add many OR conditions Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-01-10 16:03 ` Marco Colli <collimarco91@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Marco Colli @ 2020-01-10 16:03 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Justin Pryzby <pryzby@telsasoft.com>; pgsql-performance@lists.postgresql.org
Sorry, I didn't notice the SELECT * and I said something stupid...
However my reasoning should be still valid: I mean, PG could find the few
relevant rows (there's a LIMIT 30) using ONLY the index. It has all the
information required inside the index! Then it can simply access to that
rows on disk... It cannot take ~1 minute to access a few rows on disk (max
30 rows, actual 0 rows).
On Fri, Jan 10, 2020 at 4:18 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Marco Colli <collimarco91@gmail.com> writes:
> > As you can see it is a *index scan* and not an *index only* scan... I
> don't
> > understand why. The index includes all the fields used by the query... so
> > an index only scan should be possible.
>
> Huh? The query is "select * from ...", so it retrieves *all* columns
> of the table.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Bad query plan when you add many OR conditions
2020-01-10 01:11 Bad query plan when you add many OR conditions Marco Colli <collimarco91@gmail.com>
2020-01-10 03:06 ` Re: Bad query plan when you add many OR conditions Justin Pryzby <pryzby@telsasoft.com>
@ 2020-01-10 13:30 ` Marco Colli <collimarco91@gmail.com>
2020-01-13 22:14 ` Re: Bad query plan when you add many OR conditions Tomas Vondra <tomas.vondra@2ndquadrant.com>
1 sibling, 1 reply; 11+ messages in thread
From: Marco Colli @ 2020-01-10 13:30 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; pgsql-performance@lists.postgresql.org
@Justin Pryzby I have tried this as you suggested:
CREATE STATISTICS statistics_on_subscriptions_project_id_and_tags ON
project_id, tags FROM subscriptions;
VACUUM ANALYZE subscriptions;
Unfortunately nothing changes and Postgresql continues to use the wrong
plan (maybe stats don't work well on array fields like tags??).
On Fri, Jan 10, 2020 at 4:06 AM Justin Pryzby <pryzby@telsasoft.com> wrote:
> On Fri, Jan 10, 2020 at 02:11:14AM +0100, Marco Colli wrote:
> > I have a query on a large table that is very fast (0s):
> >
> https://gist.github.com/collimarco/039412b4fe0dcf39955888f96eff29db#file-fast_query-txt
>
> ORDER BY + LIMIT is a query which sometimes has issues, you can probably
> find
> more by searching. The planner thinks it'll hit the LIMIT pretty soon and
> only
> run a fraction of the index scan - but then it turns out to be wrong.
>
> You might have poor statistics on project_id and/or tags. This *might*
> help:
> ALTER TABLE subscriptions ALTER project_id SET STATISTICS 2000; ANALYZE
> subscriptions;
>
> But I'm guessing there's correlation between the two, which the planner
> doesn't
> know. If you're running at least v10, I'm guessing it would help to CREATE
> STATISTICS on those columns (and analyze).
>
> See one similar problem here (not involving LIMIT).
>
> https://www.postgresql.org/message-id/flat/CABFxtPedz4zL%2BaPWut4%2B%3Dum4av1aAXr6OVRfRB_6K7mJKMbEcw...
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Bad query plan when you add many OR conditions
2020-01-10 01:11 Bad query plan when you add many OR conditions Marco Colli <collimarco91@gmail.com>
2020-01-10 03:06 ` Re: Bad query plan when you add many OR conditions Justin Pryzby <pryzby@telsasoft.com>
2020-01-10 13:30 ` Re: Bad query plan when you add many OR conditions Marco Colli <collimarco91@gmail.com>
@ 2020-01-13 22:14 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
0 siblings, 0 replies; 11+ messages in thread
From: Tomas Vondra @ 2020-01-13 22:14 UTC (permalink / raw)
To: Marco Colli <collimarco91@gmail.com>; +Cc: Justin Pryzby <pryzby@telsasoft.com>; pgsql-performance@lists.postgresql.org
On Fri, Jan 10, 2020 at 02:30:27PM +0100, Marco Colli wrote:
>@Justin Pryzby I have tried this as you suggested:
>
>CREATE STATISTICS statistics_on_subscriptions_project_id_and_tags ON
>project_id, tags FROM subscriptions;
>VACUUM ANALYZE subscriptions;
>
>Unfortunately nothing changes and Postgresql continues to use the wrong
>plan (maybe stats don't work well on array fields like tags??).
>
We support this type of clause for extended statistics (yet).
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Bad query plan when you add many OR conditions
2020-01-10 01:11 Bad query plan when you add many OR conditions Marco Colli <collimarco91@gmail.com>
@ 2020-01-10 17:12 ` Jeff Janes <jeff.janes@gmail.com>
2 siblings, 0 replies; 11+ messages in thread
From: Jeff Janes @ 2020-01-10 17:12 UTC (permalink / raw)
To: Marco Colli <collimarco91@gmail.com>; +Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
On Thu, Jan 9, 2020 at 8:11 PM Marco Colli <collimarco91@gmail.com> wrote:
> Hello!
>
> I have a query on a large table that is very fast (0s):
>
> https://gist.github.com/collimarco/039412b4fe0dcf39955888f96eff29db#file-fast_query-txt
>
> Basically the query matches the rows that have a tag1 OR tag2 OR tag3 OR
> tag4 OR tag5...
>
Each branch of the OR is anticipated to return 400 rows, but it actually
returns 0. If it actually were to return 400 rows per branch, than
eventually the plan switch actually would make sense.
Why is the estimate off by so much? If you run a simple select, what the
actual and expected number of rows WHERE project_id = 12345? WHERE tags @>
'{crt:2018_11}'? Is one of those estimates way off reality, or is it only
the conjunction which is deranged?
> How can I encourage PostgreSQL to use the Bitmap Index Scan even when
> there are many OR conditions? I have tried with VACUUM ANALYZE
> subscriptions but it doesn't help.
>
> Note: the query is generated dynamically by customers of a SaaS, so I
> don't have full control on it
>
Do you have enough control to change the ORDER BY to:
ORDER BY ("subscriptions"."created_at" + interval '0 days') DESC
Cheers,
Jeff
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Bad query plan when you add many OR conditions
2020-01-10 01:11 Bad query plan when you add many OR conditions Marco Colli <collimarco91@gmail.com>
@ 2020-01-14 08:01 ` Thomas Kellerer <spam_eater@gmx.net>
2 siblings, 0 replies; 11+ messages in thread
From: Thomas Kellerer @ 2020-01-14 08:01 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Marco Colli schrieb am 10.01.2020 um 02:11:
> I have a query on a large table that is very fast (0s):
> https://gist.github.com/collimarco/039412b4fe0dcf39955888f96eff29db#file-fast_query-txt
>
> Basically the query matches the rows that have a tag1 OR tag2 OR tag3 OR tag4 OR tag5...
>
> However if you increase the number of OR at some point PostgreSQL makes the bad decision to change its query plan! And the new plan makes the query terribly slow:
> https://gist.github.com/collimarco/039412b4fe0dcf39955888f96eff29db#file-slow_query-txt
>
> Instead of this (which is fast):
> Bitmap Index Scan on index_subscriptions_on_project_id_and_tags
> It starts using this (which is slow):
> Parallel Index Scan using index_subscriptions_on_project_id_and_created_at
> The choice seems quite stupid since it doesn't have the tags on the new index... and indeed the query takes about 1 minute instead of a few milliseconds. Here's a list of the available indexes:
> https://gist.github.com/collimarco/039412b4fe0dcf39955888f96eff29db#file-_indexes-txt
>
> How can I encourage PostgreSQL to use the Bitmap Index Scan even when there are many OR conditions? I have tried with VACUUM ANALYZE subscriptions but it doesn't help.
>
> Note: the query is generated dynamically by customers of a SaaS, so I don't have full control on it
Can you replace the many ORs with a single "overlaps" comparison?
This
(tags @> ARRAY['crt:2018_04']::varchar[]) OR (tags @> ARRAY['crt:2018_05']::varchar[]) OR (tags @> ARRAY['crt:2018_06']::varchar[])
is equivalent to
tags && array['crt:2018_04','crt:2018_05','crt:2018_06', ...]
The && operator can make use of a GIN index so maybe that uses the index_subscriptions_on_project_id_and_tags regardless of the number of elements.
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2020-01-14 08:01 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-01-10 01:11 Bad query plan when you add many OR conditions Marco Colli <collimarco91@gmail.com>
2020-01-10 03:06 ` Justin Pryzby <pryzby@telsasoft.com>
2020-01-10 11:03 ` Marco Colli <collimarco91@gmail.com>
2020-01-10 13:34 ` Justin Pryzby <pryzby@telsasoft.com>
2020-01-10 14:53 ` Marco Colli <collimarco91@gmail.com>
2020-01-10 15:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-01-10 16:03 ` Marco Colli <collimarco91@gmail.com>
2020-01-10 13:30 ` Marco Colli <collimarco91@gmail.com>
2020-01-13 22:14 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2020-01-10 17:12 ` Jeff Janes <jeff.janes@gmail.com>
2020-01-14 08:01 ` Thomas Kellerer <spam_eater@gmx.net>
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