INDEX issues

From: "Nicholay P(dot) Chuprynin" <kolyan(at)infoport(dot)uz>
To: pgsql-admin(at)postgresql(dot)org
Subject: INDEX issues
Date: 2002-04-03 12:25:25
Message-ID: Pine.LNX.4.21.0204031643340.3259-100000@smash.infoport.uz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin


Hello, All!

Today I tried the following query on a large table (11543179 rows).

SELECT sum(raw_bytes)
FROM raw_acct
WHERE raw_date > '2001-12-31'
AND ts_client_id = 93
AND ts_server_id IS NOT NULL;

It took about 4 minutes to complete so I EXPLAINed it:

NOTICE: QUERY PLAN:

Aggregate (cost=356978.02..356978.02 rows=1 width=4)
-> Seq Scan on raw_acct (cost=0.00..356870.63 rows=42954 width=4)

EXPLAIN

I found that Postgres uses seq scan on the table even if index on
(raw_date, ts_client_id) exist.
I switched enable_seqscan to 'false', restarted server and EXPLAINed the
same query again:

NOTICE: QUERY PLAN:

Aggregate (cost=2584766.31..2584766.31 rows=1 width=4)
-> Index Scan using i_raw_date_client on raw_acct
(cost=0.00..2584658.92 rows=42954 width=4)

EXPLAIN

Now it uses index scan, but query takes MUCH more time than before (about
an hour, and it's not finished yet).
It seems very strange for me.
Can someone explain what's wrong here?
I'm using Postges 7.1.3, raw_date is DATE, ts_client_id is SMALLINT.

Thanks in advance,
Nicholay

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message William Meloney 2002-04-03 13:08:42 Thing(s) that went bump in the night...
Previous Message Gaetano Mendola 2002-04-03 09:20:31 Re: Log rotation