Forum OpenACS Development: Re: Postgres and index usage

Collapse
Posted by Don Baccus on
If anything it should be more likely to use a unique index than a non-unique index because that tells it that only one value will be returned (rather than some arbitrarily large subset of the table).

Can you post some actual code and plan EXPLAINations for us to look at?

If we can't figure out why it didn't use the original index in your insert we can post to the postgres mailing list.

It does use it for selects on openacs.org:

openacs.org=# explain select * from parties where email = 'mailto:dhogaza@pacifier.com';;
NOTICE:  QUERY PLAN:

Index Scan using parties_email_un on parties  (cost=0.00..5.98 rows=1 width=56)

EXPLAIN

Collapse
Posted by Malte Sussdorff on
Well, I dropped the index that i created and issued the same explain command again, and suddenly it didn't say "seq scan on parties" anymore. Really strange. Well, apparently the fault was on my side, so sorry for posting this. Though I still don't get it....
Collapse
Posted by Claudio Pasolini on
I have two (almost) identical servers running the same query: the first uses the index while the second scans sequentially one of the (big) joined tables.

I dropped and recreated the second db from a pg_dump of the first getting the same results.

Fortunately the first is the production server and so, after some unsuccesfull investigation, I decided to forget the problem.