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