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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
