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

"Andrus" <kobruleht2(at)hot(dot)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!