Обсуждение: INDEX issues

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

"Nicholay P. Chuprynin" <kolyan@infoport.uz> writes:
> I switched enable_seqscan to 'false', restarted server and EXPLAINed the
> same query again:
> ...
> 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.

Indexscan over a large fraction of a table is not a win compared to
a sequential scan.  Random access is just too expensive.

> Can someone explain what's wrong here?

Nothing.  The planner knew what it was doing to prefer the seqscan.

            regards, tom lane