From: | "Kendall Koning" <kkoning(at)egl(dot)net> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Index problem? |
Date: | 2000-10-03 14:46:23 |
Message-ID: | 00cf01c02d48$b369bb40$4d00a8c0@kkoning |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2000-10-03 15:11:54 | Re: alternative DB locations |
Previous Message | Jeff Hoffmann | 2000-10-03 14:44:31 | Re: alternative DB locations |