pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Markus Schaber <schabios@logi-track.com>
To: PostgreSQL Performance List <pgsql-performance@postgresql.org>
Subject: Index usage for sorted query
Date: Sat, 20 Nov 2004 15:17:10 +0100
Message-ID: <20041120151710.695f4de5@kingfisher.intern.logi-track.com> (raw)

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



view thread (4+ messages)  latest in thread

Message-ID: <20041120151710.695f4de5@kingfisher.intern.logi-track.com>
Permalink:  ../20041120151710.695f4de5@kingfisher.intern.logi-track.com/
Also on:    postgresql.org/message-id/20041120151710.695f4de5@kingfisher.intern.logi-track.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: schabios@logi-track.com
  Subject: Re: Index usage for sorted query
  In-Reply-To: <20041120151710.695f4de5@kingfisher.intern.logi-track.com>
  References: <20041120151710.695f4de5@kingfisher.intern.logi-track.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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