Re: Large data and slow queries

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.