Hello Vinny, Samuel,
Perhaps I'm missing something, but I'd be interested in the reasoning
behind this.
For column 'what', it seems you have no index on all values, only
indices with specific values for 'what'.
How does this speed up the search? Will PostgreSQL use those indices,
instead of using a generic index on 'what' and optionally other
columns?
That's a "partial index", it only contains records that meet the
requirements of the index definition.
https://www.postgresql.org/docs/9.5/static/indexes-partial.html
Basically; if you create an index on records where 'name = kees' then if
your query contains "where name=kees"
the planner can just load that index and know that the records in that
index will not contain
any other names, saving the need to filter for 'name=kees'
Martijn that is a good question. It's because we are only concerned
with a subset of events for this index and this particular query. The
query planner can recognise this and use the index correctly. By doing
this, we reduce the size of the index significantly. In the best case,
where we only wanted a few things, the index was reduced from 21GB to
8MB.
Thank for the answers, I've seen such indices before, but used, for example,
on an UPPER(...) of all values, never thought of using them to filter out
specific values in order to make the index smaller.
With regards,
Martijn Tonies
Upscene Productions
http://www.upscene.com
Database Workbench - developer tool for Oracle, MS SQL Server, PostgreSQL,
SQL Anywhere, MySQL, InterBase, NexusDB and Firebird.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
