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

Not using indexes in WHERE clauses

From: pgsql-bugs(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: Not using indexes in WHERE clauses
Date: 2001-01-19 12:42:15
Message-ID: 200101191242.f0JCgFX33104@hub.org (view raw or flat)
Thread:
Lists: pgsql-bugs
Marcin Zukowski (eru(at)mimuw(dot)edu(dot)pl) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
Not using indexes in WHERE clauses

Long Description
PGSQL doesn't use indexes in WHERE clauses.

Tested on version 7.0.3:
PostgreSQL 7.0.3 on i586-pc-linux-gnu, compiled by gcc egcs-2.91.66

explain query from example returns:
Seq Scan on tab4  (cost=0.00..3576.00 rows=99999 width=8)

It DID work on:
PostgreSQL 6.5.2 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66

where explain query returned:
Index Scan using tab4_pkey on tab4  (cost=2565.67 rows=66667 width=8)

Vacuuming didn't help.
So, it's quite similar to the bug:
Bug Report #10 from 2000-08-23 17:53:13-04
but solution (changing to int4 and vacuuming) didn't help.

Sample Code
CREATE TABLE tab4 (
  id INT4 PRIMARY KEY,
  val INT4
);
-- Load 200000 records with values ((0,0),(1,1),... :) )
COPY tab4 FROM '/home/postgres/pg/tab4.txt';
CREATE INDEX tab4_id2 ON tab4 (val);

-- THIS IS THE POINT
explain SELECT * FROM tab4 WHERE id > 100000 ;



No file was uploaded with this report


Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2001-01-19 15:47:26
Subject: Re: Not using indexes in WHERE clauses
Previous:From: Tom LaneDate: 2001-01-19 07:08:34
Subject: Re: ascii() picks up sign bit past CHAR value 127

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