Re: Can PostgreSQL use multi-column index for FK constraint validation?

On 01/26/2016 12:47 PM, Dane Foster wrote:
> My example is modeling an order details table and the answer to the
> question of selectivity is it depends. For some of our clients it is
> highly selective because customers generally order a single item at a
> time. For others it's multi-modal because it starts out w/ their
> customers ordering only a single item but over time customer behavior
> changes and there is this mix of single and multi item orders.
> Additionally my use case for PostgreSQL is the VPS use case where each
> client has their own schema so I'd prefer not to have to deal w/ per
> client index building and maintenance. So is there a rule of thumb
> design wise for variable selectivity as I've described?

Well, my general perspective is that if the table has millions of rows
(or more), and there are 100's (or more) of col3 items for each
col1/col2 combo, then I'd *probably* add a specific FK index.

Given the "I don't know" you have above, I generally wouldn't add one,
and then look at response times on updates/deletes to the orders table
to see if there's a performance issue.

--
Josh Berkus
Red Hat OSAS
(opinions are my own)