Thread: Query problem fixed

I figured out how to make the query faster.  There should be a mailing list
set up for wasted questions since I always seem to figure out the problem
after I've bugged everyone for help.

In the query

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)

Since the data was being ordered by column3 it seems to have sped the query
up to 1 ms from 6000ms by making column 3 the first part of the index rather
than the second.

Kevin

The thing I can't really understand why can't the planner find out something
like this:

1. Index scan using column6
2. Backward search on subset using column3

Any guru to explain?

G.
------------------------------- cut here -------------------------------
----- Original Message -----
From: "Kevin Schroeder" <mirage@mirageworks.com>
Sent: Thursday, May 29, 2003 4:09 PM


> I figured out how to make the query faster.  There should be a mailing
list
> set up for wasted questions since I always seem to figure out the problem
> after I've bugged everyone for help.
>
> In the query
>
> 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)
>
> Since the data was being ordered by column3 it seems to have sped the
query
> up to 1 ms from 6000ms by making column 3 the first part of the index
rather
> than the second.
>
> Kevin

"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