Skip site navigation (1) Skip section navigation (2)

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

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 (view raw or flat)
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!

In response to

Responses

pgsql-performance by date

Next:From: PFCDate: 2008-11-22 23:24:52
Subject: Re: Hash join on int takes 8..114 seconds
Previous:From: Glyn AstillDate: 2008-11-22 22:00:11
Subject: Re: Perc 3 DC

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group