pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Query problem fixed
3+ messages / 3 participants
[nested] [flat]

* Query problem fixed
@ 2003-05-29 14:09 Kevin Schroeder <mirage@mirageworks.com>
  2003-05-29 14:27 ` Re: Query problem fixed SZUCS Gábor <surrano@mailbox.hu>
  2003-05-29 14:49 ` Re: Query problem fixed Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 3+ messages in thread

From: Kevin Schroeder @ 2003-05-29 14:09 UTC (permalink / raw)
  To: pgsql-performance

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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Query problem fixed
  2003-05-29 14:09 Query problem fixed Kevin Schroeder <mirage@mirageworks.com>
@ 2003-05-29 14:27 ` SZUCS Gábor <surrano@mailbox.hu>
  1 sibling, 0 replies; 3+ messages in thread

From: SZUCS Gábor @ 2003-05-29 14:27 UTC (permalink / raw)
  To: pgsql-performance

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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Query problem fixed
  2003-05-29 14:09 Query problem fixed Kevin Schroeder <mirage@mirageworks.com>
@ 2003-05-29 14:49 ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 3+ messages in thread

From: Tom Lane @ 2003-05-29 14:49 UTC (permalink / raw)
  To: Kevin Schroeder <mirage@mirageworks.com>; +Cc: 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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2003-05-29 14:49 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-29 14:09 Query problem fixed Kevin Schroeder <mirage@mirageworks.com>
2003-05-29 14:27 ` SZUCS Gábor <surrano@mailbox.hu>
2003-05-29 14:49 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox