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.
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;
I've examined the plans, and the planner keeps using a filter. For example
explain analyse select alexa_contactinfo___companystockticker___symbol_dom from domain_dom
where alexa_contactinfo___companystockticker___symbol_dom = 'AAA';
"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)"
"Total runtime: 223340.892 ms"
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