Postgres - jakie indeksy?
Szarak - 20-01-2007 00:01
Postgres - jakie indeksy?
Witam
Mam dwie tabele:
1. documents:
id int4 NOT NULL,
subject varchar,
CONSTRAINT documents_pkey PRIMARY KEY (id)
2. fields:
id int4 NOT NULL,
docid int4 NOT NULL,
name varchar NOT NULL,
value varchar,
CONSTRAINT fields_pkey PRIMARY KEY (id),
CONSTRAINT "$1" FOREIGN KEY (docid)
REFERENCES documents (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
Czyli dla każdego dokumentu może istnieć wiele pól o konkretnej nazwie i
wartości. Dojdzie jeszcze klucz sprawdzający unikalność par docid i name.
Utworzyłem zapytanie, które dla wybranych dokumentów, oprócz tytułu,
zwraca również wartość dwóch pól o nazwie: pole1 i pole2. Tak wygląda:
SELECT d.id, d.subject, f.value AS pole1, f2.value AS pole2
FROM documents d
LEFT JOIN fields f ON d.id=f.docid AND f.name='pole1'
LEFT JOIN fields f2 ON d.id=f2.docid AND f2.name='pole2'
WHERE d.id>2 AND d.id < 6;
Tworzyłem już różne klucze w tabeli fields (m.in. łącznie na docid i
name) i za każdym razem, gdy robię explane zapytania, pokazuje, że na
tabeli fields robiony jest seq scan przy każdym joinie. Jaki indeks
utworzyć, żeby był wykorzystywany w tym zapytaniu? Da się w ogóle?
Pozdrawiam
Szarak
Szarak - 20-01-2007 00:01
Dnia 19.01.2007 23:13 Szarak napisał(a):
> Tworzyłem już różne klucze w tabeli fields ...
^^^^^^
Oczywiście chodzi o indeksy :)
Pozdrawiam
Szarak
Misiekd - 20-01-2007 00:01
Dnia Fri, 19 Jan 2007 23:13:34 +0100, Szarak napisał(a):
> [...]
ja bym zrobił taki
CREATE INDEX index1_idx ON fields (docid, name)
tylko zauważ, że żeby baza korzystała z indeksów to musi być trochę więcej
niż 10 rekordów
--
Pozdrawiam Misiekd
Szarak - 20-01-2007 00:25
Dnia 19.01.2007 23:52 Misiekd napisał(a):
> ja bym zrobił taki
> CREATE INDEX index1_idx ON fields (docid, name)
Tak też zrobiłem, co zresztą napisałem w pierwszym poście)
> tylko zauważ, że żeby baza korzystała z indeksów to musi być trochę więcej
> niż 10 rekordów
Tabela documents zawiera ok 11 tys. rekordów, a tabela fields 110 tys.
Przy takim zapytaniu jak podałem (WHERE d.id>2 AND d.id < 6;
), pierwszy join jest po indeksie, a drugi już seq scan. Jeśli zmienię 6
na 10, to już oba są seq scan. Wtedy dostaję taki plan:
Merge Left Join (cost=5415.45..5514.04 rows=686 width=23)
Merge Cond: ("outer".id = "inner".docid)
-> Merge Left Join (cost=2707.73..2755.46 rows=79 width=18)
Merge Cond: ("outer".id = "inner".docid)
-> Index Scan using documents_pkey on documents d
(cost=0.00..3.13 rows=9 width=13)
Index Cond: ((id > 2) AND (id < 10))
-> Sort (cost=2707.73..2729.62 rows=8759 width=9)
Sort Key: f.docid
-> Seq Scan on fields f (cost=0.00..2134.16 rows=8759
width=9)
Filter: ((name)::text = 'pole1'::text)
-> Sort (cost=2707.73..2729.62 rows=8759 width=9)
Sort Key: f2.docid
-> Seq Scan on fields f2 (cost=0.00..2134.16 rows=8759 width=9)
Filter: ((name)::text = 'pole2'::text)
Czy w ogóle możliwe jest zrobienie tego lepiej? Czy taką strukturę bazy
mogę wyrzucić do kosza?
Pozdrawiam
Szarak
Misiekd - 20-01-2007 00:40
Dnia Sat, 20 Jan 2007 01:23:50 +0100, Szarak napisał(a):
> [...]
to pokaż jeszcze plan dla d.id < 6
dodaj oprócz
CREATE INDEX index1_idx ON fields (docid, name)
jeszcze
CREATE INDEX index2_idx ON fields (name)
i też pokaż plan
BTW ile rekordów zwraca zapytanie d.id < 6 a ile d.id < 10?
--
Pozdrawiam Misiekd
Szarak - 20-01-2007 01:01
Dnia 20.01.2007 01:38 Misiekd napisał(a):
> to pokaż jeszcze plan dla d.id < 6
Merge Left Join (cost=2707.73..4621.84 rows=460 width=23)
Merge Cond: ("outer".id = "inner".docid)
-> Nested Loop Left Join (cost=0.00..1865.58 rows=53 width=18)
-> Index Scan using documents_pkey on documents d
(cost=0.00..3.07 rows=6 width=13)
Index Cond: (id < 6)
-> Index Scan using "$2" on fields f (cost=0.00..310.31 rows=9
width=9)
Index Cond: ("outer".id = f.docid)
Filter: ((name)::text = 'pole1'::text)
-> Sort (cost=2707.73..2729.62 rows=8759 width=9)
Sort Key: f2.docid
-> Seq Scan on fields f2 (cost=0.00..2134.16 rows=8759 width=9)
Filter: ((name)::text = 'pole2'::text)
Śmiesznie, nie?:) Jeden join po indeksie, a drugi seq :)
> dodaj oprócz
> CREATE INDEX index1_idx ON fields (docid, name)
> jeszcze
> CREATE INDEX index2_idx ON fields (name)
> i też pokaż plan
To zrobiłem już wcześniej i plan, który widziałeś jest właśnie z tym
indeksem. Nic to nie zmienia, drugi indeks nie jest używany.
> BTW ile rekordów zwraca zapytanie d.id < 6 a ile d.id < 10?
Indeksy są liczone od 1 co 1, czyli odpowiednio 5 i 9.
Dodam jeszcze, że to Postgres 7.4.7. Być może 8 lepiej sobie z tym
radzi, ale w tej chwili nie mam możliwości sprawdzenia. Poza tym szukam
rozwiązania, które mogłoby pójść na kilku popularniejszych rdb.
Pozdrawiam
Szarak
=?ISO-8859-2?Q?Pawe=B3_Matejski?= - 21-01-2007 00:21
Szarak wrote:
> Dnia 20.01.2007 01:38 Misiekd napisał(a):
>> to pokaż jeszcze plan dla d.id < 6
>
> Merge Left Join (cost=2707.73..4621.84 rows=460 width=23)
> Merge Cond: ("outer".id = "inner".docid)
> -> Nested Loop Left Join (cost=0.00..1865.58 rows=53 width=18)
> -> Index Scan using documents_pkey on documents d
> (cost=0.00..3.07 rows=6 width=13)
> Index Cond: (id < 6)
> -> Index Scan using "$2" on fields f (cost=0.00..310.31 rows=9
> width=9)
> Index Cond: ("outer".id = f.docid)
> Filter: ((name)::text = 'pole1'::text)
> -> Sort (cost=2707.73..2729.62 rows=8759 width=9)
> Sort Key: f2.docid
> -> Seq Scan on fields f2 (cost=0.00..2134.16 rows=8759 width=9)
> Filter: ((name)::text = 'pole2'::text)
>
> Śmiesznie, nie?:) Jeden join po indeksie, a drugi seq :)
Śmieszne? Przecież są różne warunki.
Zobacz, że za pierwszym razem spodziewa się 9 rekordów, a za drugim prawie 9k.
Jest różnica, nie?
Zrób explain analize, to będzie można coś więcej powiedzieć.
--
P.M.
Szarak - 23-01-2007 00:01
Dnia 20.01.2007 02:49 Paweł Matejski napisał(a):
> Śmieszne? Przecież są różne warunki.
> Zobacz, że za pierwszym razem spodziewa się 9 rekordów, a za drugim prawie 9k.
> Jest różnica, nie?
No widzę właśnie, ale nie wiem dlaczego. 'pole1' jest praktycznie tyle
samo co 'pole2'. Tyle, że za pierwszym razem warunek ogranicza do
2<id<10, a za drugim razem nie.
> Zrób explain analize, to będzie można coś więcej powiedzieć.
Może Tobie coś powie, mi nic więcej nie mówi:
Merge Left Join (cost=2707.73..3062.60 rows=5 width=24) (actual
time=560.738..561.081 rows=7 loops=1)
Merge Cond: ("outer".id = "inner".docid)
-> Nested Loop Left Join (cost=0.00..1555.17 rows=5 width=19)
(actual time=0.128..0.414 rows=7 loops=1)
-> Index Scan using documents_pkey on documents d
(cost=0.00..3.08 rows=5 width=14) (actual time=0.055..0.098 rows=7 loops=1)
Index Cond: ((id > 2) AND (id < 10))
-> Index Scan using "$2" on fields f (cost=0.00..310.31 rows=9
width=9) (actual time=0.025..0.032 rows=0 loops=7)
Index Cond: ("outer".id = f.docid)
Filter: ((name)::text = 'pole1'::text)
-> Sort (cost=2707.73..2729.62 rows=8759 width=9) (actual
time=560.510..560.515 rows=5 loops=1)
Sort Key: f2.docid
-> Seq Scan on fields f2 (cost=0.00..2134.16 rows=8759
width=9) (actual time=0.029..495.080 rows=11004 loops=1)
Filter: ((name)::text = 'pole2'::text)
Total runtime: 566.111 ms
Spróbuję puścić to na nowszym postgresie, może będzie sobie z tym lepiej
radził.
Pozdrawiam
Szarak
Szarak - 23-01-2007 00:01
Dnia 22.01.2007 18:56 Szarak napisał(a):
> Spróbuję puścić to na nowszym postgresie, może będzie sobie z tym lepiej
> radził.
Tak jak myślałem, to samo na 8.2.1 wygląda tak:
Nested Loop Left Join (cost=4.82..1748.50 rows=55 width=100) (actual
time=0.121..4.132 rows=97 loops=1)
-> Nested Loop Left Join (cost=4.82..912.03 rows=55 width=68)
(actual time=0.102..2.208 rows=97 loops=1)
-> Bitmap Heap Scan on documents d (cost=4.82..75.57 rows=55
width=36) (actual time=0.060..0.133 rows=97 loops=1)
Recheck Cond: ((id > 2) AND (id < 100))
-> Bitmap Index Scan on documents_pkey (cost=0.00..4.80
rows=55 width=0) (actual time=0.044..0.044 rows=97 loops=1)
Index Cond: ((id > 2) AND (id < 100))
-> Index Scan using "$2" on fields f2 (cost=0.00..15.17 rows=3
width=36) (actual time=0.015..0.016 rows=1 loops=97)
Index Cond: ((d.id = f2.docid) AND ((f2.name)::text =
'pole2'::text))
-> Index Scan using "$2" on fields f (cost=0.00..15.17 rows=3
width=36) (actual time=0.013..0.015 rows=1 loops=97)
Index Cond: ((d.id = f.docid) AND ((f.name)::text = 'pole1'::text))
Total runtime: 4.389 ms
Pozdrawiam
Szarak
zanotowane.pldoc.pisz.plpdf.pisz.pleffulla.pev.pl
|
[PostgreSQL] - jak =?ISO-8859-2?Q?zabezpieczy=E6_interesy_tw?==?ISO-8859-2?Q?=F3rcy_systemu_=3F=3F=3F?=
postgresql - int/int
postgresql Select count(*) czy raczej Select count(ID)
[PostgreSQL] jak =?ISO-8859-2?Q?pobra=E6_warto=B6=E6_zwracan?==?ISO-8859-2?Q?=B1_przez_funkcj=EA=3F?=
[postgresql] INSERT OR UPDATE - jak =?ISO-8859-2?Q?b=EAdzie_na?==?ISO-8859-2?Q?jlepiej=3F?=
[postgresql] kilka =?ISO-8859-2?Q?rekord=F3w_subquery_jako_?==?ISO-8859-2?Q?string?=
[PostgreSQL] Jak =?ISO-8859-2?Q?po=B3=B1czy=E6_funkcje_z_w?==?ISO-8859-2?Q?idokiem?=
Postgres - replikcja master-master
Dopasowanie do "najlepszego" dopasowania :) [ PostgreSQL]
Problemy z =?ISO-8859-2?Q?instalacj=B1_PostgreSQL_na_syste?==?ISO-8859-2?Q?mach_Windows?=
zanotowane.pldoc.pisz.plpdf.pisz.plponland.htw.pl
Cytat
Decede mihi sole - nie zasłaniaj mi słonca.
Gdy kogoś kochasz, jesteś jak stworzyciel świata - na cokolwiek spojrzysz, nabiera to kształtu, wypełnia się barwą, światłem. Powietrze przytula się do ciebie, choćby był mróz, a ty masz w sobie tyle radości, że musisz ją rozdawać wokoło, bo się w tobie nie mieści
Hoc fac - tak czyń.
A tergo - od tyłu; z tyłu.
I czarne włosy posiwieją. Safona |
|
