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 по дате отправления: