[HN Gopher] Faceting large result sets in PostgreSQL
       ___________________________________________________________________
        
       Faceting large result sets in PostgreSQL
        
       Author : rubiquity
       Score  : 31 points
       Date   : 2022-12-06 20:40 UTC (2 hours ago)
        
 (HTM) web link (www.cybertec-postgresql.com)
 (TXT) w3m dump (www.cybertec-postgresql.com)
        
       | djbusby wrote:
       | Surprised 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 wrote:
         | And you have some control over when that scan happens, as well
         | as the ability to index the MV
        
       | fabian2k wrote:
       | Faceting 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?
        
       ___________________________________________________________________
       (page generated 2022-12-06 23:01 UTC)