From: | Gregory Stark <stark(at)enterprisedb(dot)com> |
---|---|
To: | "Andrus" <kobruleht2(at)hot(dot)ee> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: seq scan over 3.3 million rows instead of single key index access |
Date: | 2008-11-22 23:03:50 |
Message-ID: | 87k5avz7l5.fsf@oxford.xeocode.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
"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!
From | Date | Subject | |
---|---|---|---|
Next Message | PFC | 2008-11-22 23:24:52 | Re: Hash join on int takes 8..114 seconds |
Previous Message | Glyn Astill | 2008-11-22 22:00:11 | Re: Perc 3 DC |