Re: seq scan over 3.3 million rows instead of single key index access

"Andrus" <kobruleht2@hot.ee> writes:

> There are  indexes on   rid(dokumnr) and dok(dokumnr) and dokumnr is int.
> Instead of using single key index, 8.1.4 scans over whole rid table.
> Sometimes idtelluued can contain more than single row so replacing join with
> equality is not possible.
>
> How to fix ?

Firstly the current 8.1 release is 8.1.15. Any of the bugs fixed in those 11
releases might be related to this.

Secondly:

> CREATE TEMP TABLE idtellUued(dokumnr INT) ON COMMIT DROP;
> INSERT INTO idtellUued VALUES(1249228);
> explain analyze  select 1
>   from dok JOIN rid USING(dokumnr)
> JOIN idtellUued USING(dokumnr)
>
> "              ->  Seq Scan on idtelluued  (cost=0.00..31.40 rows=2140 width=4)
> (actual time=0.006..0.011 rows=1 loops=1)"

The planner thinks there are 2,140 rows in that temporary table so I don't
believe this is from the example posted. I would suggest running ANALYZE
idtellUued at some point before the problematic query.


--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's Slony Replication support!