Thread: Index problem?

Index problem?

From
"Kendall Koning"
Date:
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