On Thu, Dec 19, 2019 at 11:28:55AM -0800, Jeremy Schneider wrote: > On 12/19/19 08:12, Bruce Momjian wrote: > > I don't see lossy BRIN indexes helping with the uniqueness use-case, and > > I am not sure they would help with the rare case either. They would > > help for range-based partitions, but I thought our existing facilities > > worked in that case. > > Correlated data. The existing facilities work if the filtering column > is exactly the same as the partition column. But it's not at all > uncommon to have other columns with correlated data, perhaps the most > obvious of which is timeseries tables with many date columns of various > definitions (row first update, row latest update, invoice date, payment > date, process date, ship date, etc). > > What if you could use *two* indexes in a single execution plan? Use the > global BRIN to narrow down to 2 or 3 out of a hundred or more > partitions, then use local indexes to find specific rows in the > partitions of interest? That might work, without being too overly > complicated.
No, that is very interesting --- having secondary indexes for partitioned tables that trim most partitions. Would index lookups on each partition index be very slow? BRIN indexes? I am assuming global indexes would only avoid such lookups. -- Bruce Momjian <br...@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
