Re: Query problem fixed - Mailing list pgsql-performance

From Tom Lane
Subject Re: Query problem fixed
Date
Msg-id 17680.1054219783@sss.pgh.pa.us
Whole thread Raw
In response to Query problem fixed  ("Kevin Schroeder" <mirage@mirageworks.com>)
List pgsql-performance
"Kevin Schroeder" <mirage@mirageworks.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

pgsql-performance by date:

Previous
From: SZUCS Gábor
Date:
Subject: Re: Query problem fixed
Next
From: "scott.marlowe"
Date:
Subject: Re: Select query takes long to execute