pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Index usage for sorted query
4+ messages / 3 participants
[nested] [flat]

* Index usage for sorted query
@ 2004-11-20 14:17 Markus Schaber <schabios@logi-track.com>
  2004-11-20 16:12 ` Re: Index usage for sorted query Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
  2004-11-20 16:48 ` Re: Index usage for sorted query Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 4+ messages in thread

From: Markus Schaber @ 2004-11-20 14:17 UTC (permalink / raw)
  To: pgsql-performance

Hello,

I have the following query plan:

logigis=# explain SELECT geom, ref_in_id as ref, nref_in_id as nref, st_name as name, substr(l_postcode,1,2) as postfirst,  func_class as level FROM schabi.streets WHERE cd='ca' ORDER BY l_postcode;
                                            QUERY PLAN                                             
---------------------------------------------------------------------------------------------------
 Sort  (cost=2950123.42..2952466.07 rows=937059 width=290)
   Sort Key: l_postcode
   ->  Index Scan using streets_name_idx on streets  (cost=0.00..2857177.57 rows=937059 width=290)
         Index Cond: ((cd)::text = 'ca'::text)


And I have, beside others, the following index:
    »streets_name_idx« btree (cd, l_postcode)

As the query plan shows, my postgresql 7.4 does fine on using the index
for the WHERE clause.

But as it fetches all the rows through the index, why doesn't it
recognize that, fetching this way, the rows are already sorted by
l_postcode?

As I have a larger set of data, it nearly breaks down our developer
machine every time we do this, as it always creates a temporary copy of
the large amount of data to sort it (setting sort_mem higher makes it
swap, setting it lower makes it thrashing disk directly).

Is Postgresql 8 more intelligend in this case?

Thanks for your hints,
Markus 

-- 
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios@logi-track.com | www.logi-track.com



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

* Re: Index usage for sorted query
  2004-11-20 14:17 Index usage for sorted query Markus Schaber <schabios@logi-track.com>
@ 2004-11-20 16:12 ` Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
  2004-11-22 15:01   ` Re: Index usage for sorted query Markus Schaber <schabios@logi-track.com>
  1 sibling, 1 reply; 4+ messages in thread

From: Pierre-Frédéric Caillaud @ 2004-11-20 16:12 UTC (permalink / raw)
  To: Markus Schaber <schabios@logi-track.com>; pgsql-performance


Instead of :

> WHERE cd='ca' ORDER BY l_postcode;

Write :

> WHERE cd='ca' ORDER BY cd, l_postcode;

You have a multicolumn index, so you should specify a multicolumn sort  
exactly the same as your index, and the planner will get it.



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

* Re: Index usage for sorted query
  2004-11-20 14:17 Index usage for sorted query Markus Schaber <schabios@logi-track.com>
  2004-11-20 16:12 ` Re: Index usage for sorted query Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
@ 2004-11-22 15:01   ` Markus Schaber <schabios@logi-track.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Markus Schaber @ 2004-11-22 15:01 UTC (permalink / raw)
  To: Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>; +Cc: pgsql-performance

Hi, Pierre-Frédéric,

On Sat, 20 Nov 2004 17:12:43 +0100
Pierre-Frédéric Caillaud <lists@boutiquenumerique.com> wrote:

> > WHERE cd='ca' ORDER BY l_postcode;
> 
> Write :
> 
> > WHERE cd='ca' ORDER BY cd, l_postcode;
> 
> You have a multicolumn index, so you should specify a multicolumn sort  
> exactly the same as your index, and the planner will get it.

Thanks, that seems to help.

Seems weird to order by a column that is all the same value, but well,
why not :-)

Thanks a lot,
Markus


-- 
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios@logi-track.com | www.logi-track.com



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

* Re: Index usage for sorted query
  2004-11-20 14:17 Index usage for sorted query Markus Schaber <schabios@logi-track.com>
@ 2004-11-20 16:48 ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 4+ messages in thread

From: Tom Lane @ 2004-11-20 16:48 UTC (permalink / raw)
  To: Markus Schaber <schabios@logi-track.com>; +Cc: pgsql-performance

Markus Schaber <schabios@logi-track.com> writes:
> But as it fetches all the rows through the index, why doesn't it
> recognize that, fetching this way, the rows are already sorted by
> l_postcode?

Tell it to "ORDER BY cd, l_postcode".

> Is Postgresql 8 more intelligend in this case?

No.

			regards, tom lane



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


end of thread, other threads:[~2004-11-22 15:01 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-11-20 14:17 Index usage for sorted query Markus Schaber <schabios@logi-track.com>
2004-11-20 16:12 ` Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
2004-11-22 15:01   ` Markus Schaber <schabios@logi-track.com>
2004-11-20 16:48 ` 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