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
@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 a
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 subscription
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
Marco Colli 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
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.
On Thu, Jan 9, 2020 at 8:11 PM Marco Colli 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
