"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.
> 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.
Ask me about EnterpriseDB's Slony Replication support!
In response to
pgsql-performance by date
|Next:||From: PFC||Date: 2008-11-22 23:24:52|
|Subject: Re: Hash join on int takes 8..114 seconds|
|Previous:||From: Glyn Astill||Date: 2008-11-22 22:00:11|
|Subject: Re: Perc 3 DC|