Thread: forcing pg to use an index

Hello -

I'm using PG 8.2.3 on an 8-processor server with 32GB of memory.  The table in question has about 22.6M rows.

One of the fields in the table is mostly null.  Only about 9K rows have values.  Of those 9K, about half of of them occur only once.  The rest occur between 2 and 76 times, so the distribution is highly skewed.

I've defined a partial b-tree index on the field where the value is not null, but the planner doesn't seem to use it.
 

I've examined the plans, and the planner keeps using a filter.  For example

 "Seq Scan on domain_dom  (cost=0.00..1422366.60 rows=1 width=32) (actual time=223139.526..223340.822 rows=1 loops=1)"
"  Filter: (alexa_contactinfo___companystockticker___symbol_dom = 'AAA'::text)"

It seems to me that the query would run a lot faster if the system would just use the index to go to the rows (there's only one in this case) for which the value is 'AAA'.

I tried turning seqscan off with set enable_seqscan = off, but that didn't help.

Any suggestions/help would be appreciated. 

Thank you,
David

"David Monarchi" <david.e.monarchi@gmail.com> writes:
> CREATE INDEX domain_alexa_stock_ticker_dom ON domain_dom
>   USING btree (alexa_contactinfo___companystockticker___symbol_dom)
>   WHERE NOT alexa_contactinfo___companystockticker___symbol_dom IS NULL;

Try spelling that as "foo is not null", instead.  There's a limit to the
complexity of the planner's predicate-proving ability ...

            regards, tom lane