On Thu, 14 Feb 2002, Tom Lane wrote: > Brian McCane <[EMAIL PROTECTED]> writes: > > CREATE INDEX foo_index ON foo (bazid, score desc) ; > > > Which would be exactly what I want, and would complete in a split second. > > Instead, this thing runs FOREVER (okay, it just seems that way to my > > client :). Is there any way to get the equivalent index from PostgreSQL? > > You don't need a funny index, you just need to get the planner to notice > that that index can serve to create the desired output ordering. Try > > create table foo(bazid int, score int); > CREATE INDEX foo_index ON foo (bazid, score) ; > > explain select * from foo where bazid = 123456 > order by bazid desc, score desc limit 100 ; > > NOTICE: QUERY PLAN: > > Limit (cost=0.00..17.07 rows=5 width=8) > -> Index Scan Backward using foo_index on foo (cost=0.00..17.07 rows=5 width=8) > > EXPLAIN > > > regards, tom lane >
Thanks, That did it. I tried the explain with "score desc, bazid desc" and it looked the same to me. I had myself convinced it had to be that way. Then I tried your way (after I realized I had already selected a specific bazid so it didn't matter if it was descending :), and it was all okay. -brian Wm. Brian McCane | Life is full of doors that won't open Search http://recall.maxbaud.net/ | when you knock, equally spaced amid those Usenet http://freenews.maxbaud.net/ | that open when you don't want them to. Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber" ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])