Problem with indexes

Started by Przemyslaw Kowalczykover 24 years ago2 messagesgeneral
Jump to latest
#1Przemyslaw Kowalczyk
przemyslaw.kowalczyk@engine.com.pl

Hi.

I'm trying to manage apache access logs with Postgresql (7.1). I've parsed
last month access logs for one of our sites into database. There are about
900 000 records. Then I've created index:

stats=# create index log_date on log(date);
CREATE

Then I've tried to select something from the table browsing on date, it
took about 3 minutes to get the result. So I've tried explain:

stats=# explain SELECT ip FROM log WHERE date = 1000733984 ;
NOTICE: QUERY PLAN:

Seq Scan on log (cost=0.00..52859.15 rows=9008 width=12)

EXPLAIN
Why is there seq scan, even though I've created index?

Structure of the table
stats=# \d log
Table "log"
Attribute | Type | Modifier
------------+-------------------------+----------
ip | character varying(20) | not null
date | bigint | not null
date_human | character varying(50) |
request | character varying(1024) | not null
response | character varying(3) |
size | bigint | not null
ref_inner | character varying(256) |
ref_outer | character varying(1024) |
browser | character varying(256) |
iduser | character varying(50) |
type | character varying(1) |
Indices: log_browser,
log_date,
log_iduser,
log_ip,
log_ref_inner,
log_ref_outer,
log_request,
log_response,
log_type

Postgresql runs on linux 2.4.12.

Regards
Przem

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Przemyslaw Kowalczyk (#1)
Re: Problem with indexes

Przemyslaw Kowalczyk <przemyslaw.kowalczyk@engine.com.pl> (by way of Przemyslaw Kowalczyk <przemyslaw.kowalczyk@engine.com.pl>) writes:

stats=# explain SELECT ip FROM log WHERE date = 1000733984 ;
NOTICE: QUERY PLAN:

Seq Scan on log (cost=0.00..52859.15 rows=9008 width=12)

(1) a VACUUM ANALYZE seems called for;

(2) what is the datatype of the date column? If it's not int4 then you
may need to quote or cast the constant.

regards, tom lane