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

Re: Query problem fixed

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Kevin Schroeder" <mirage(at)mirageworks(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Query problem fixed
Date: 2003-05-29 14:49:43
Message-ID: 17680.1054219783@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
"Kevin Schroeder" <mirage(at)mirageworks(dot)com> writes:
> select row_key, column1, column2, column3, column4, column5 from table1
> where column6 = 1 order by column3 desc limit 21;

> I changed the index to

> message_index btree (column3, column6)

> rather than

> message_index btree (column6, column3, column7)

That's probably not the best solution.  It would be better to leave the
index with column6 first and write the query as

... where column6 = 1 order by column6 desc, column3 desc limit 21

This doesn't change the results (since there's only one value of column6
in the output), but it does cause the planner to realize that a
backwards scan of the index would produce what you want with no sort
step.  The results should be essentially instantaneous if you can get
the query plan down to Index Scan Backward + Limit.

			regards, tom lane

In response to

pgsql-performance by date

Next:From: scott.marloweDate: 2003-05-29 16:01:16
Subject: Re: Select query takes long to execute
Previous:From: SZUCS GáborDate: 2003-05-29 14:27:10
Subject: Re: Query problem fixed

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