7.1.3 not using index

From: Daniel Kalchev <daniel(at)digsys(dot)bg>
To: pgsql-hackers(at)postgresql(dot)org
Subject: 7.1.3 not using index
Date: 2001-12-03 16:59:45
Message-ID: 200112031659.SAA20679@dcave.digsys.bg
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello,

After fresh install of PostgreSQL 7.1.3 I was having one particular query
(JOIN), running for several hours. Upon closer investigation, it was returning
weird EXPLAIN 'optimisations' (in essence, doing 'index' searches on fields
that were not constrained in the query etc). The same query has reasonable
EXPLAIN and executes fine under 7.0.2.

I tried to re-create the table by table, starting with the following:

CREATE TABLE r (
a integer,
b integer,
c integer,
d integer
);

CREATE INDEX r_d_idx on r(d);

COPY r FROM stdin;
1 4234 4324 4
1 4342 886 8
[...]
\.

(table has ~30k rows)

EXPLAIN SELECT * FROM r where d = 8;

The result is

NOTICE: QUERY PLAN:

Seq Scan on r (cost=0.00...3041.13 rows=7191 width=4)

Does not matter if I VACUUM ANALYZE the table or the whole database.

Any ideas why this happens?

PostgreSQL is compiled with

./configure --enable-locale --with-perl --with-python --with-tcl --enable-odbc
--with-krb4 --with-openssl --enable-syslog --with-includes=/usr/include/kerbero
sIV:/usr/contrib/include

this, under BSD/OS 4.2

Thanks in advance for any ideas,
Daniel

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Thomas Lockhart 2001-12-03 17:14:21 Re: Second call for platform testing
Previous Message Tom Lane 2001-12-03 16:08:28 Re: Call for platform testing