From: lars <lhofhansl@yahoo.com>
To: pgsql-performance@postgresql.org
Subject: Statistics and Multi-Column indexes
Date: Sun, 10 Jul 2011 14:16:06 -0700
Message-ID: <4E1A1696.5010307@yahoo.com> (raw)
I know this has been discussed various times...
We are maintaining a large multi tenant database where *all* tables have
a tenant-id and all indexes and PKs lead with the tenant-id.
Statistics and counts for the all other columns are only really
meaningful within the context of the tenant they belong to.
There appear to be five options for me:
1. Using single column indexes on all interesting columns and rely on
PostgreSQLs bitmap indexes to combine them (which are pretty cool).
2. Use multi column indexes and accept that sometimes Postgres pick the
wrong index (because a non-tenant-id
column might seem highly selective over the table, but it is not for a
particular tenant - or vice versa).
3. Use a functional index that combines multiple columns and only query
via these, that causes statistics
gathering for the expression.
I.e. create index i on t((tenantid||column1)) and SELECT ... FROM t
WHERE tenantid||column1 = '...'
4. Play with n_distinct and/or set the statistics for the inner columns
to some fixed values that lead to the plans that we want.
5. Have a completely different schema and maybe a database per tenant.
Currently we use Oracle and query hinting, but I do not like that
practice at all (and Postgres does not have hints anyway).
Are there any other options?
#1 would be the simplest, but I am concerned about the overhead, both
maintaining two indexes and building the bitmap during queries - for
every query.
I don't think #2 is actually an option. We have some tenants with many
(sometimes 100s) millions of rows per table,
and picking the wrong index would be disastrous.
Could something like #3 be generally added to Postgres? I.e. if there is
a multi column index keep combined statistics for
the involved columns. Of course in that case is it no longer possible to
query the index by prefix.
#3 also seems expensive as the expression needs to be evaluated for each
changed row.
Still trying #4. I guess it involves setting the stat target for the
inner columns to 0 and then inserting my own records into
pg_statistic. Probably only setting n_distinct, i.e. set it "low" if the
inner column is not selective within the context of a tenant and "high"
otherwise.
For various reasons #5 is also not an option.
And of course the same set of questions comes up with joins.
Thanks.
-- Lars
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lhofhansl@yahoo.com
Subject: Re: Statistics and Multi-Column indexes
In-Reply-To: <4E1A1696.5010307@yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox