Seq Scan but I think it should be Index Scan

From: Edoceo Lists <lists(at)edoceo(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Seq Scan but I think it should be Index Scan
Date: 2005-10-27 01:55:54
Message-ID: 436033AA.9030900@edoceo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

So the details of it:
I'm using PostgreSQL 8.0.3 on a Gentoo kernel 2.6.12 on a P4/2.8G+HT proc, with kernel RAID0 on some SATA drives and 1G
RAM. Don't know the bus speed. I'm thinking that my queries are not using indexs correctly and therefore taking longer
to complete than they should. I've put the details below, but changed some names. If anyone could shed some light?

pg_config --configure
'--prefix=/usr' '--mandir=/usr/share/man' '--host=i686-pc-linux-gnu' '--with-docdir=/usr/share/doc/postgresql-8.0.3'
'--libdir=/usr/lib' '--enable-depend' '--with-gnu-ld' '--with-perl' '--with-openssl' '--enable-nls' '--with-pam'
'CFLAGS=-O2 -march=pentium4' 'host_alias=i686-pc-linux-gnu'

data=# explain analyze select count(id) from x_base where x_type < 100 and x_date<='2005-10-26' and x_time<'06:00:00';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=539453.36..539453.36 rows=1 width=4) (actual time=66200.763..66200.764 rows=1 loops=1)
-> Seq Scan on x_base (cost=0.00..539136.18 rows=126871 width=4) (actual time=31618.624..66174.710 rows=37451 loops=1)
Filter: ((x_type < 100) AND (x_date <= '2005-10-26'::date) AND (x_time < '06:00:00'::time without time zone))
Total runtime: 66200.811 ms

data=# \d x_base
Table "public.x_base"
Column | Type | Modifiers
---------------------+------------------------+----------------------------------------------------------
id | integer | not null default nextval('public.x_base_id_seq'::text)
x_code | character(8) |
x_date | date | not null
x_time | time without time zone | not null
a | character(1) |
b | integer |
c | character(5) |
d | character(16) |
e | character(1) |
f | character(1) |
g | character(10) |
h | character(1) |
i | character(1) |
j | character varying(32) |
k | integer |
l | integer |
m | integer |
n | character varying(32) |
o | integer |
p | character varying(14) |
q | integer |
Indexes:
"x_base_pkey" PRIMARY KEY, btree (id)
"ix_d_cd" btree (x_date)
"ix_t_cb" btree (x_type)
Foreign-key constraints:
"fk_k_id" FOREIGN KEY (k) REFERENCES x_file(id)

Now, see that x_type index? Why didn't this thing Index Scan "ix_t_cb" on that column? Me thinks if it had my query
would be much faster. Or perhaps if I only where x_type? I tried that but it still took a minute. I took out count()
and it still took a minute. Always using Seq Scan, am I doing something dumb here? There are more than six million
records in that table, maybe thats just how long it takes? Perhaps I should change architecture or schema to improve
performance? Tweak the log? Thanks.

/djb

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bob Pawley 2005-10-27 02:47:51 Re: Error Message
Previous Message Cristian Prieto 2005-10-27 01:54:27 Re: Variable return type...