Re: [PERFORM] Index usage for sorted query

2004-11-22 Thread Markus Schaber
Hi, Pierre-Frédéric, On Sat, 20 Nov 2004 17:12:43 +0100 Pierre-Frédéric Caillaud <[EMAIL PROTECTED]> 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

Re: [PERFORM] Index usage for sorted query

2004-11-20 Thread Tom Lane
Markus Schaber <[EMAIL PROTECTED]> 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.

Re: [PERFORM] Index usage for sorted query

2004-11-20 Thread Pierre-Frédéric Caillaud
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. ---(end of broadcast)

[PERFORM] Index usage for sorted query

2004-11-20 Thread Markus Schaber
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