Witam, trafiłem ostatnio na dziwne zachowanie w widoku Postgresql. Dokumentacja od bazy danych na ten temat milczy, w necie są podobne tematy, ale nie znalazłem niczego, co wiązałoby się z widokami. Ale po kolei, mam takie dwie tabele:
CREATE TABLE limit_test.dane
(
id serial NOT NULL,
kolumna1 integer NOT NULL,
wartosc text NOT NULL,
CONSTRAINT pkey_id PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE limit_test.dane
OWNER TO postgres;
CREATE TABLE limit_test.dane2
(
id serial NOT NULL,
dane_id integer NOT NULL,
CONSTRAINT pkey_dane2_id PRIMARY KEY (id),
CONSTRAINT fkey_dane_id FOREIGN KEY (dane_id)
REFERENCES limit_test.dane (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
ALTER TABLE limit_test.dane2
OWNER TO postgres;
oraz widok:
CREATE OR REPLACE VIEW limit_test.widok AS
SELECT d.id AS d_id,
d.kolumna1,
d.wartosc,
dd.dane_id,
dd.id AS dd_id
FROM limit_test.dane d
LEFT JOIN limit_test.dane2 dd ON dd.dane_id = d.id
ORDER BY d.id
LIMIT 200;
ALTER TABLE limit_test.widok
OWNER TO postgres;
**Proszę nie analizujcie do czego ja to wykorzystuje, chodzi tylko o pokazanie problemu, które występuje również na tak małych tabelach. W rzeczywistym systemie danych i tabel jest o wiele więcej. **
Każda tabela ma po 1 mln rekordów. I teraz, kiedy robię SELECT na widoku dla konkretnego d_id, czyli np.
SELECT * FROM limit_test.widok WHERE d_id = 1;
to takie zapytanie wykonuje się średnio 7-8 SEKUND! Ale jeśli zapytanie z definicji widoku wykonam bezpośrednio, czyli
SELECT d.id AS d_id,
d.kolumna1,
d.wartosc,
dd.dane_id,
dd.id AS dd_id
FROM limit_test.dane d
LEFT JOIN limit_test.dane2 dd ON dd.dane_id = d.id
WHERE d.id = 1
ORDER BY d.id
LIMIT 200;
Wynik dostaje już po ok. 200-300 ms.
Idąc dalej, jeśli zmodyfikuję widok w taki sposób, że usunę LIMIT, to nagle poprzednie zapytanie na widoku
SELECT * FROM limit_test.widok WHERE d_id = 1;
wykonuje się "normalnie" szybko, czyli ok 200-300 ms.
Wykonałem też analize zapytania
EXPLAIN ANALYZE SELECT * FROM limit_test.widok WHERE d_id = 1;
ale wyniki są dla mnie mało zrozumiałe.
'Subquery Scan on widok (cost=120630.28..120633.28 rows=1 width=22) (actual time=40780.634..40785.527 rows=1 loops=1)'
' Filter: (widok.d_id = 1)'
' Rows Removed by Filter: 199'
' -> Limit (cost=120630.28..120630.78 rows=200 width=22) (actual time=40780.563..40784.238 rows=200 loops=1)'
' -> Sort (cost=120630.28..123130.28 rows=1000000 width=22) (actual time=40780.536..40781.681 rows=200 loops=1)'
' Sort Key: d.id'
' Sort Method: top-N heapsort Memory: 31kB'
' -> Hash Right Join (cost=32789.00..77411.00 rows=1000000 width=22) (actual time=13454.629..34326.238 rows=1000000 loops=1)'
' Hash Cond: (dd.dane_id = d.id)'
' -> Seq Scan on dane2 dd (cost=0.00..14425.00 rows=1000000 width=8) (actual time=0.851..6638.054 rows=1000000 loops=1)'
' -> Hash (cost=15406.00..15406.00 rows=1000000 width=14) (actual time=13417.709..13417.709 rows=1000000 loops=1)'
' Buckets: 4096 Batches: 64 Memory Usage: 608kB'
' -> Seq Scan on dane d (cost=0.00..15406.00 rows=1000000 width=14) (actual time=0.039..6555.656 rows=1000000 loops=1)'
'Total runtime: 40786.241 ms'
**Czy ktoś może mi wyjaśnić, czemu dodanie LIMIT do takiego widoku powoduje tak dramatyczne zwolnienie jego pracy? ** Przecież LIMIT generalnie ma na celu zmniejszenie "obszaru" przeszukiwań i w zapytaniach bardziej przyspiesza niż zwalnia cały proces.
To wszystko testowałem na wersji Postgresql 9.2 i 9.3 i na obu jest ten sam efekt.