Skip site navigation (1) Skip section navigation (2)

Re: help with dual indexing

From: Orion Henry <orion(at)trustcommerce(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: help with dual indexing
Date: 2004-01-26 20:53:16
Message-ID: 1075150396.10503.363.camel@orthanc (view raw or flat)
Thread:
Lists: pgsql-performance
Thanks Tom!  You're a life-saver.

On Fri, 2004-01-23 at 17:08, Tom Lane wrote:
> Orion Henry <orion(at)trustcommerce(dot)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
-- 
Orion Henry <orion(at)trustcommerce(dot)com>

In response to

pgsql-performance by date

Next:From: PC DrewDate: 2004-01-26 21:00:16
Subject: Insert Times
Previous:From: Bill MoranDate: 2004-01-26 17:17:41
Subject: Re: On the performance of views

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group