Re: help with dual indexing - Mailing list pgsql-performance

From Tom Lane
Subject Re: help with dual indexing
Date
Msg-id 8630.1074906490@sss.pgh.pa.us
Whole thread Raw
In response to help with dual indexing  (Orion Henry <orion@trustcommerce.com>)
Responses Re: help with dual indexing  (Orion Henry <orion@trustcommerce.com>)
List pgsql-performance
Orion Henry <orion@trustcommerce.com> writes:
> The queries usually are in the form of, where "user_id = something and
> event_time between something and something".

> Half of my queries index off of the user_id and half index off the
> event_time.  I was thinking this would be a perfect opportunity to use a
> dual index of (user_id,event_time) but I'm confused as to weather this
> will help

Probably.  Put the user_id as the first column of the index --- if you
think about the sort ordering of a multicolumn index, you will see why.
With user_id first, a constraint as above describes a contiguous
subrange of the index; with event_time first it does not.

            regards, tom lane

pgsql-performance by date:

Previous
From: "Joshua D. Drake"
Date:
Subject: Re: Slow delete times??
Next
From: "Joshua D. Drake"
Date:
Subject: Re: High Performance/High Reliability File system on SuSE64