Index problem?
| От | Kendall Koning |
|---|---|
| Тема | Index problem? |
| Дата | |
| Msg-id | 00cf01c02d48$b369bb40$4d00a8c0@kkoning обсуждение исходный текст |
| Список | pgsql-general |
I'm having trouble optimizing a specific query. I've created indexes, but
when I do an explain on the query, it still shows I'm going through a
sequential scan inside of a nested loop... (I think)
Would anyone be willing to point out where I've screwed up here, or suggest
a more efficent way to go about it?
Any help would be much appreciated.
--
Kendall Koning
Senior Network Engineer, egl.net
Ph: (616) 392-9949 x26
ipacct=> \d daily
Table "daily"
Attribute | Type | Modifier
-----------+--------+----------
day | date | not null
address | cidr | not null
bytesin | bigint |
bytesout | bigint |
Indices: daily_address,
daily_pkey
ipacct=> \d allocations
Table "allocations"
Attribute | Type | Modifier
-----------+-------------+----------
custid | integer | not null
address | cidr | not null
note | varchar(40) |
Indices: allocations_address,
allocations_customer,
allocations_pkey
ipacct=> explain SELECT allocations.custid, daily."day",
allocations.address, int4((sum(daily.bytesin) / 1000)) AS kbin,
int4((sum(daily.bytesout) / 1000)) AS kbout FROM allocations, daily WHERE
(daily.address <<= allocations.address) GROUP BY allocations.custid,
daily."day", allocations.address;
NOTICE: QUERY PLAN:
Aggregate (cost=10359.70..11166.10 rows=6451 width=48)
-> Group (cost=10359.70..10843.54 rows=64512 width=48)
-> Sort (cost=10359.70..10359.70 rows=64512 width=48)
-> Nested Loop (cost=0.00..3980.08 rows=64512 width=48)
-> Seq Scan on allocations (cost=0.00..1.04 rows=4
width=16)
-> Seq Scan on daily (cost=0.00..591.56 rows=32256
width=32)
EXPLAIN
В списке pgsql-general по дате отправления: