From: Tom Lane <tgl@sss.pgh.pa.us>
To: Gregory Stark <stark@enterprisedb.com>
Cc: Andrus <kobruleht2@hot.ee>
Cc: pgsql-performance@postgresql.org
Subject: Re: seq scan over 3.3 million rows instead of single key index access
Date: Sat, 22 Nov 2008 18:58:42 -0500
Message-ID: <16660.1227398322@sss.pgh.pa.us> (raw)
In-Reply-To: <87k5avz7l5.fsf@oxford.xeocode.com>
References: <gg9pg7$1edv$2@news.hub.org>
<87k5avz7l5.fsf@oxford.xeocode.com>
Gregory Stark <stark@enterprisedb.com> writes:
> "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.
If this can still be reproduced in 8.1.15 it would be worth looking into.
My first guess is that there are multiple relevant indexes on the big
table and the old bugs in choose_bitmap_and() are making it mess up.
> 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.
No, that's a pretty likely default assumption for a never-vacuumed,
never-analyzed table. Your advice is correct though.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, stark@enterprisedb.com, kobruleht2@hot.ee
Subject: Re: seq scan over 3.3 million rows instead of single key index access
In-Reply-To: <16660.1227398322@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox