Problem with indexes
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
I think you have to do a vacuum analize.
For very small tables seq scan is faster then index scan.
After vacuum analize postgres will know the table size.
--- Przemyslaw Kowalczyk <przemyslaw.kowalczyk@engine.com.pl> wrote:
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:
<SNIP>
=====
Chris Albertson
Home: 310-376-1029 chrisalbertson90278@yahoo.com
Cell: 310-990-7550
Office: 310-336-5189 Christopher.J.Albertson@aero.org
__________________________________________________
Do You Yahoo!?
Check out Yahoo! Shopping and Yahoo! Auctions for all of
your unique holiday gifts! Buy at http://shopping.yahoo.com
or bid at http://auctions.yahoo.com