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

Re: help with dual indexing

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Orion Henry <orion(at)trustcommerce(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: help with dual indexing
Date: 2004-01-24 01:08:10
Message-ID: 8630.1074906490@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Christopher WeimannDate: 2004-01-24 01:22:47
Subject: Re: High Performance/High Reliability File system on SuSE64
Previous:From: Joshua D. DrakeDate: 2004-01-24 00:55:28
Subject: Re: Slow delete times??

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