4 ms·
Faceting large result sets in PostgreSQL
- fabian2k 4y agoFaceting is really useful, but quite expensive if implemented in a naive way in Postgres. This looks very interesting, but the really annoying thing about requiring a Postgres extension is that you can't use it with most managed Postgres services. What is the best way to calculate facets in Postgres when you can't rely on the ability to install an extension? The naive way would be a bunch of "count(*) group by" queries, a slightly less naive way would be to do the same using grouping sets if possible. But are there any other tricks one could use to calculate facets for large, but not impossibly large numbers of rows?
- danielheath 4y ago‘select count(1) filter by x, count(1) filter by y ‘ might be faster?
- SahAssar 4y agoThe faceting extension itself is pure sql, but it does depend on pg_roaringbitmap which is a C extension.
- djbusby 4y agoSurprised this doesn't mention Materialized Views. Those can be used to create the buckets/facets from the larger table then offer faster lookup of the facet values. Creating the MView would still be from a sequential scan but then you'll have small/fast views to queue queries for the huge table.
- jaxn 4y agoAnd you have some control over when that scan happens, as well as the ability to index the MV
- canadiantim 4y agoThis looks brilliant! Amazing work
- justkez 4y agoI'm implementing some faceting in a project I'm working on. I have some nested `jsonb` columns which include categorical variables (to quote this article). The table is only 5 million rows and isn't likely to grow significantly, but it is not very wide. The performance is okay and I was about to experiment with flattening out the table (putting all categorical vars into their own columns) and indexing that way vs. indexing JSONb paths. I'm not super familiar with the postgres internals to know if this is better or worse so wondering if anyone could share any similar experience?!