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
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
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
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):
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
@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
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:
