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
