Powolne skanowanie indeksu w dużym stole

Korzystając z PostgreSQL 9.2, mam problemy z powolnymi zapytaniami na stosunkowo dużej tabeli (ponad 200 milionów wierszy). Nie próbuję niczego szalonego, tylko dodając wartości historyczne. Poniżej znajduje się zapytanie i dane wyjściowe planu zapytania.

Mój układ stołu:

                                   Table "public.energy_energyentry"
  Column   |           Type           |                            Modifiers
-----------+--------------------------+-----------------------------------------------------------------
 id        | integer                  | not null default nextval('energy_energyentry_id_seq'::regclass)
 prop_id   | integer                  | not null
 timestamp | timestamp with time zone | not null
 value     | double precision         | not null
Indexes:
    "energy_energyentry_pkey" PRIMARY KEY, btree (id)
    "energy_energyentry_prop_id" btree (prop_id)
    "energy_energyentry_prop_id_timestamp_idx" btree (prop_id, "timestamp")
Foreign-key constraints:
    "energy_energyentry_prop_id_fkey" FOREIGN KEY (prop_id) REFERENCES gateway_peripheralproperty(id) DEFERRABLE INITIALLY DEFERRED

Zakres danych wynosi od 01.01.2012 do chwili obecnej, a nowe dane są ciągle dodawane. W prop_idkluczu obcym jest około 2,2 tys. Różnych wartości , rozmieszczonych równomiernie.

Zauważam, że szacunki wierszy nie są dalekie, ale szacunki kosztów wydają się większe czterokrotnie. To chyba nie jest problem, ale czy mogę coś z tym zrobić?

Spodziewam się, że problemem może być dostęp do dysku, ponieważ tabela nie jest cały czas w pamięci.

EXPLAIN ANALYZE 
SELECT SUM("value") 
FROM "energy_energyentry" 
WHERE 
  "prop_id"=82411 
  AND "timestamp">'2014-06-11' 
  AND "timestamp"<'2014-11-11'
;

 Aggregate  (cost=214481.45..214481.46 rows=1 width=8) (actual time=51504.814..51504.814 rows=1 loops=1)
   ->  Index Scan using energy_energyentry_prop_id_timestamp_idx on  energy_energyentry (cost=0.00..214434.08 rows=18947 width=8) (actual time=136.030..51488.321 rows=13578 loops=1)
         Index Cond: ((prop_id = 82411) AND ("timestamp" > '2014-06-11 00:00:00+00'::timestamp with time zone) AND ("timestamp" < '2014-11-11 00:00:00+00'::timestamp with time zone))
 Total runtime: 51504.841 ms

Wszelkie sugestie, jak to zrobić szybciej?
Czuję się dobrze, słysząc, że nie zrobiłem nic dziwnego.

Twój stół jest duży , podobnie jak każdy indeks obejmujący cały stół. Przy założeniu, że:

• wprowadzane są tylko nowe dane (z timestamp = now())
• istniejące wiersze nie są ani zmieniane, ani usuwane.
• masz dane od 01.01.2012, ale zapytania dotyczą głównie bieżącego roku (?)

Sugerowałbym częściowy, wielokolumnowy (obejmujący!) Indeks :

CREATE INDEX ON energy_energyentry (prop_id, "timestamp", value)
WHERE "timestamp" >= '2014-01-01 0:0';  -- adapt to your needs

Uwzględnij tylko zakres czasowy, który jest regularnie sprawdzany. Skuteczność pogarsza się z czasem dzięki nowym wpisom. Od czasu do czasu odtwarzaj indeks. (Być może trzeba będzie dostosować swoje zapytania.) Zobacz połączoną odpowiedź poniżej.

Ostatnia wartość kolumny jest uwzględniona tylko w celu uzyskania z niej skanów indeksowych . Agresywne ustawienie auto próżni może pomóc w utrzymaniu aktualności mapy widoczności, jak wspomniano już o @jjanes .

Indeks częściowy powinien łatwiej pasować do pamięci RAM i pozostać tam dłużej.

Konieczne może być uwzględnienie tego WHEREwarunku w zapytaniach, aby planista zrozumiał, że indeks ma zastosowanie do zapytania, na przykład:

SELECT sum(value) AS sum_value
FROM   energy_energyentry
WHERE  prop_id = 82411 
AND   "timestamp" > '2014-06-11 0:0' 
AND   "timestamp" < '2014-11-11 0:0'
AND   "timestamp" >= '2014-01-01 0:0'; -- seems redundant, but may be needed

Ponieważ zapytanie sumuje wiele wierszy ( rows=13578), zajmie to trochę czasu, nawet w przypadku skanowania tylko indeksu. Jednak nie powinno to być blisko 50 sekund. Mniej niż sekunda na jakimkolwiek przyzwoitym sprzęcie.

Powiązane _{(ale zignoruj CLUSTERi FILLFACTORoba są nieistotne, jeśli możesz uzyskać z tego tylko indeksy)} :

• Optymalizacja indeksu z datami

Poza tym:
ponieważ obecnie masz indeks (prop_id, "timestamp"), dodatkowy indeks po prostu (prop_id)może kosztować więcej niż jest wart:

• Czy indeks złożony jest również przydatny w przypadku zapytań dotyczących pierwszego pola?

Jeśli utworzysz indeks na (prop_id, „znacznik czasu”, „wartość”), wówczas może użyć skanu tylko do indeksu, aby obliczyć wartość bez odwiedzania tabeli. To może zaoszczędzić wiele losowego dostępu do dysku.

Aby uzyskać jak najwięcej korzyści, musisz być agresywny w odkurzaniu stołu. Domyślne ustawienia autovac nie są wystarczająco agresywne w przypadku tabel tylko z wstawieniami, w których chcesz efektywnie obsługiwać skanowanie tylko z indeksem.