[PERFORM] Index usage for sorted query
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:[EMAIL PROTECTED] | www.logi-track.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Index usage for sorted query
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)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Index usage for sorted query
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. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] index use
Arshavir, Thanks for all the replies. It actually has to do with the locales. The db where the index gets used is running on C vs the the other one that uses en_US.UTF-8. I guess the db with the wrong locale will need to be waxed and recreated with correct locale settings. I wonder if there are any plans to make LIKE work with all locales. I thought there were some fixes for this in 8.0, but I can't find anything in the release notes. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] tablespace + RAM disk?
David, But, I'm also still interested in the answer to my question: is there any reason you could not put an 8.0 tablespace on a RAM disk? Some people have *talked* about trying it, but nobody yet has reported back. I can see a few potential problems: 1) The query planner would not be aware, and could not be made aware short of hacking the source, that one tablespace has different access speeds than the others; 2) After a crash, you might be unable to recover that tablespace, and PG would refuse to bring the system back up without it. However, the best thing to do is to try it. Good luck, and do a write-up for us! -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
