Useless index

From: Brian McCane <bmccane(at)mccons(dot)net>
To: pg-admin <pgsql-admin(at)postgresql(dot)org>
Subject: Useless index
Date: 2002-02-14 13:59:54
Message-ID: 20020214074001.N83588-100000@fw.mccons.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

HELP!!!

Okay, now that I have your attention :). I have a table with
about 560M rows in it. Performance is usually pretty good except when I
do a specific query:

SELECT fooid FROM foo
WHERE bazid = 123456
ORDER BY score DESC LIMIT 100 ;

My index looks like:

CREATE INDEX foo_index ON foo (bazid, score) ;

This query should return the 100 highest scores out a possible 1.1M rows
(for the query that got me attention). When I do an explain, I get:

# explain select fooid from foo where bazid = 123456 order by score desc
limit 100 ;
NOTICE: QUERY PLAN:

Limit (cost=1552.10..1552.10 rows=100 width=6)
-> Sort (cost=1552.10..1552.10 rows=382 width=6)
-> Index Scan using foo_index on foo (cost=0.00..1535.69
rows=382 width=6)

EXPLAIN

Doesn't look too bad, but I have a LOT of data where there is only a
single fooid for a bazid, which really skews the EXPLAIN results. On
mysql (and I believe Oracle, gotta find my other hat), I could create the
index as:

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?
This is a major show stopper for me at this point. I have looked through
Chapter 7 of the 'idocs', but I didn't find anything that would help.

- 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"

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Roy Cabaniss 2002-02-14 14:43:53 Re: restoring template1
Previous Message Jean-Michel POURE 2002-02-14 13:21:00 Re: Locale support