From: | Marco Colli <collimarco91(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: Bad query plan when you add many OR conditions |
Date: | 2020-01-10 11:03:39 |
Message-ID: | CAFvCgN7Kf35VDEu_e8f-qfUjq-2vhs21rbc1UJqEfos0=TMcUw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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(at)telsasoft(dot)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%40mail.gmail.com
>
From | Date | Subject | |
---|---|---|---|
Next Message | Marco Colli | 2020-01-10 13:30:27 | Re: Bad query plan when you add many OR conditions |
Previous Message | Justin Pryzby | 2020-01-10 03:06:02 | Re: Bad query plan when you add many OR conditions |