From: Ragnar <gnari@hive.is>
To: Bryce Nesbitt <bryce1@obviously.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Sequential scan where Index scan expected (update)
Date: Fri, 03 Mar 2006 09:31:37 +0000
Message-ID: <1141378297.18656.119.camel@localhost.localdomain> (raw)
In-Reply-To: <4407F031.6050706@obviously.com>
References: <244004377.20060302124132@isb.com.pl>
<2873.1141312882@sss.pgh.pa.us>
<4407F031.6050706@obviously.com>
On fim, 2006-03-02 at 23:28 -0800, Bryce Nesbitt wrote:
> I'm getting sequential scans (and poor performance), on scans using my
> primary keys. This is an older postgres.
> Can anyone help figure out why?
>
>
> demo=# \d xx_thing
> Table "public.xx_thing"
> Column | Type | Modifiers
> -------------------------+-----------------------------+-----------
> thing_id | bigint | not null
...
> demo=# explain update xx_thing_event set thing_color='foo' where
> thing_event_id=10000;
> QUERY PLAN
> ---------------------------------------------------------------------
> Seq Scan on xx_thing_event (cost=0.00..5842.48 rows=1 width=110)
...
> demo=# select * from version();
> version
> ----------------------------------------------------------------------------------------------------------
> PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
try one of:
... WHERE thing_event_id=10000::bigint
... WHERE thing_event_id='10000'
or upgrade to >= 8.0
your problem is that the 10000 in 'thing_event_id=10000' is parsed as an
integer but not a bigint, so the planner
does not find a matching index. so you either have to
scpecify ::bigint or ::int8 in the query or quote the number, which will
cause postgres to cast it to the
column's type.
if you try
... WHERE thing_event_id=10000000000
you will see the index used because this number cannot
be a int4 so is parsed as a bigint.
newer versions of Postgresql can deal with this.
gnari
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-sql@postgresql.org
Cc: gnari@hive.is, bryce1@obviously.com
Subject: Re: Sequential scan where Index scan expected (update)
In-Reply-To: <1141378297.18656.119.camel@localhost.localdomain>
* 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