====== PostgreSQL Index Types ======

Not everything should be a B-Tree {{/dokuwiki/lib/images/smileys/icon_wink.gif|;-)}}

https://postgresql.us/events/pgopen2019/sessions/session/647/slides/45/look-it-up.pdf

For example, 'bloom' indexes might be useful in case of dynamic filters like this:

<code>
WHERE picalike_brand='soliver' and picalike_gender='women' and shop_id='some_de_crawler'
</code>
The advantage over compound indexes is that you do not need to create one for every possible subset.


===== Take a Deeper Look =====

To analyze the values and distribution of a certain column in a relation:

<code>
\x
select * from pg_stats where tablename='new_filters' and attname='picalike_brand' limit 1;
</code>

==== Abuse FTS ====

When the goal is to use generic filters like

<code>
'a & b', 'a | b' or 'a - b'
</code>
and several columns are possible, it might be useful to use the ::tsvector, ::tsquery types. Those are mainly for full text search, but they allow to perform search queries like

<code>
'pg_women & pc_clothing__dresses'
</code>
in a very compact and efficient format.

But extra care must be taken, since to_tsvector transforms the data, so in our case, just pass a string to insert like that

<code>
'pg_women pg_clothing__dresses'::tsvector
</code>
. Some special characters like '&' and ':' are not allowed.

Finally, one can use

<code>
SELECT * FROM dingens where vector_column @@ 'pg_women & pc_clothing__dresses
</code>
to lookup everything that contains both filters.


===== Bloom =====

https://www.postgresql.org/docs/12/bloom.html

Trade-off: N btree indexes vs. 1 bloom index .. space versus time


====== Cluster Tables with an Index ======

To remove the clustering from a table

<code>
alter table oneshot set without cluster;
</code>
Reorganize a table by the order induced by some index

<code>
 cluster filters using picalike_category
</code>
Refresh a clustering

<code>
cluster filters
</code>
More info: https://www.postgresql.org/docs/12/sql-cluster.html


===== Include columns into a cluster =====

HINT: not possible with GIN indexes {{/dokuwiki/lib/images/smileys/icon_sad.gif|:-(}}

<code>
create index multi_meta on oneshot using gin ("multi") include ("metadata");
</code>
→ ERROR: access method “gin” does not support included columns