problem z czasem wykonania zapytania
Melwin - 01-08-2007 00:00
problem z czasem wykonania zapytania
Mam takie zapytanie:
select grupa,model,dzal,dekor,opis,ntp,p,m,zl,sum(ile) as ile from tre
where m='Prod' and (ZL IS NULL OR ZL ='200707311')
and MODEL not LIKE 'ŁAWA%' and MODEL not LIKE 'STOLIK%' and MODEL not
LIKE 'STÓŁ%' and MODEL <>'IMPERIUM' and MODEL <> 'MONTANA' and MODEL <>
'VENETO' and MODEL <> 'DOMINO' and MODEL <> 'SPARTA' and model<>'WARKA'
and model<>'SPARTAN'
and model not like 'CONF ŁAWA%' and model <> 'REKLAMACJA ŁAWA'
and GRUPA<>'TAPI' and GRUPA<>'KUCH' and zam is not null
group by grupa,model,dzal,dekor,opis,ntp,p,m,zl
order by 1,2,3,4,5,6
explain:
QUERY PLAN
Sort (cost=109246.24..109247.76 rows=607 width=115) (actual
time=33156.710..33157.223 rows=713 loops=1)
Sort Key: grupa, model, dzal, dekor, opis, ntp
-> HashAggregate (cost=109210.59..109218.18 rows=607 width=115)
(actual time=33140.705..33141.989 rows=713 loops=1)
-> Bitmap Heap Scan on tre (cost=3809.47..109059.02 rows=6063
width=115) (actual time=29946.169..33117.341 rows=2500 loops=1)
Recheck Cond: ((m)::text = 'Prod'::text)
Filter: (((zl IS NULL) OR ((zl)::text =
'200707311'::text)) AND ((model)::text !~~ 'ŁAWA%'::text) AND
((model)::text !~~ 'STOLIK%'::text) AND ((model)::text !~~
'STÓŁ%'::text) AND ((model)::text <> 'IMPERIUM'::text) AND
((model)::text <> 'MONTANA'::text) AND ((model)::text <> 'VENETO'::text)
AND ((model)::text <> 'DOMINO'::text) AND ((model)::text <>
'SPARTA'::text) AND ((model)::text <> 'WARKA'::text) AND ((model)::text
<> 'SPARTAN'::text) AND ((model)::text !~~ 'CONF ŁAWA%'::text) AND
((model)::text <> 'REKLAMACJA ŁAWA'::text) AND ((grupa)::text <>
'TAPI'::text) AND ((grupa)::text <> 'KUCH'::text) AND (zam IS NOT NULL))
-> Bitmap Index Scan on tre_idx16 (cost=0.00..3809.47
rows=552991 width=0) (actual time=2228.503..2228.503 rows=553946 loops=1)
Index Cond: ((m)::text = 'Prod'::text)
Total runtime: 33158.803 ms
jesli usune element: ZL IS NULL czyli:
select grupa,model,dzal,dekor,opis,ntp,p,m,zl,sum(ile) as ile from tre
where m='Prod' and (ZL ='200707311')
and MODEL not LIKE 'ŁAWA%' and MODEL not LIKE 'STOLIK%' and MODEL not
LIKE 'STÓŁ%' and MODEL <>'IMPERIUM' and MODEL <> 'MONTANA' and MODEL <>
'VENETO' and MODEL <> 'DOMINO' and MODEL <> 'SPARTA' and model<>'WARKA'
and model<>'SPARTAN'
and model not like 'CONF ŁAWA%' and model <> 'REKLAMACJA ŁAWA'
and GRUPA<>'TAPI' and GRUPA<>'KUCH' and zam is not null
group by grupa,model,dzal,dekor,opis,ntp,p,m,zl
order by 1,2,3,4,5,6
explain:
QUERY PLAN
Sort (cost=572.76..572.79 rows=12 width=115) (actual
time=118.949..119.094 rows=204 loops=1)
Sort Key: grupa, model, dzal, dekor, opis, ntp
-> HashAggregate (cost=572.40..572.55 rows=12 width=115) (actual
time=114.662..115.000 rows=204 loops=1)
-> Index Scan using tre_idx12 on tre (cost=0.00..569.47
rows=117 width=115) (actual time=23.813..112.684 rows=274 loops=1)
Index Cond: (((m)::text = 'Prod'::text) AND ((zl)::text =
'200707311'::text))
Filter: (((model)::text !~~ 'ŁAWA%'::text) AND
((model)::text !~~ 'STOLIK%'::text) AND ((model)::text !~~
'STÓŁ%'::text) AND ((model)::text <> 'IMPERIUM'::text) AND
((model)::text <> 'MONTANA'::text) AND ((model)::text <> 'VENETO'::text)
AND ((model)::text <> 'DOMINO'::text) AND ((model)::text <>
'SPARTA'::text) AND ((model)::text <> 'WARKA'::text) AND ((model)::text
<> 'SPARTAN'::text) AND ((model)::text !~~ 'CONF ŁAWA%'::text) AND
((model)::text <> 'REKLAMACJA ŁAWA'::text) AND ((grupa)::text <>
'TAPI'::text) AND ((grupa)::text <> 'KUCH'::text) AND (zam IS NOT NULL))
Total runtime: 119.388 ms
tre_idx12 na polach: m,zl,grupa,zam
tre_idx16 na polach: m
oba typu btree czyli powinny poprawnie indeksowac wartosci null.
dlaczego tak sie dzieje ? Jak zoptymalizowac to pierwsze zapytanie ?
wloochacz - 01-08-2007 00:00
[ciach]
> dlaczego tak sie dzieje ? Jak zoptymalizowac to pierwsze zapytanie ?
I znowu nie napisałeś, że chodzi o PostgreSQL... ;-)
--
wloochacz
=?ISO-8859-2?Q?Pawe=B3_Matejski?= - 01-08-2007 00:00
Melwin wrote:
> Mam takie zapytanie:
A czemu nie napisałeś do jakiej bazy?
> select grupa,model,dzal,dekor,opis,ntp,p,m,zl,sum(ile) as ile from tre
> where m='Prod' and (ZL IS NULL OR ZL ='200707311')
> and MODEL not LIKE 'ŁAWA%' and MODEL not LIKE 'STOLIK%' and MODEL not
> LIKE 'STÓŁ%' and MODEL <>'IMPERIUM' and MODEL <> 'MONTANA' and MODEL <>
> 'VENETO' and MODEL <> 'DOMINO' and MODEL <> 'SPARTA' and model<>'WARKA'
> and model<>'SPARTAN'
> and model not like 'CONF ŁAWA%' and model <> 'REKLAMACJA ŁAWA'
> and GRUPA<>'TAPI' and GRUPA<>'KUCH' and zam is not null
> group by grupa,model,dzal,dekor,opis,ntp,p,m,zl
> order by 1,2,3,4,5,6
Jeśli chcesz pomocy od kogoś, czy nie lepiej jest jak najbardziej ułatwić
zrozumienie Twojego problemu odpowiadającym - sformatować ładnie zapytanie.
> explain:
> QUERY PLAN
> Sort (cost=109246.24..109247.76 rows=607 width=115) (actual
> time=33156.710..33157.223 rows=713 loops=1)
> Sort Key: grupa, model, dzal, dekor, opis, ntp
> -> HashAggregate (cost=109210.59..109218.18 rows=607 width=115)
> (actual time=33140.705..33141.989 rows=713 loops=1)
> -> Bitmap Heap Scan on tre (cost=3809.47..109059.02 rows=6063
> width=115) (actual time=29946.169..33117.341 rows=2500 loops=1)
> Recheck Cond: ((m)::text = 'Prod'::text)
> Filter: (((zl IS NULL) OR ((zl)::text =
> '200707311'::text)) [...]
> -> Bitmap Index Scan on tre_idx16 (cost=0.00..3809.47
> rows=552991 width=0) (actual time=2228.503..2228.503 rows=553946 loops=1)
> Index Cond: ((m)::text = 'Prod'::text)
> Total runtime: 33158.803 ms
>
> jesli usune element: ZL IS NULL czyli:
>
> explain:
> QUERY PLAN
> Sort (cost=572.76..572.79 rows=12 width=115) (actual
> time=118.949..119.094 rows=204 loops=1)
> Sort Key: grupa, model, dzal, dekor, opis, ntp
> -> HashAggregate (cost=572.40..572.55 rows=12 width=115) (actual
> time=114.662..115.000 rows=204 loops=1)
> -> Index Scan using tre_idx12 on tre (cost=0.00..569.47
> rows=117 width=115) (actual time=23.813..112.684 rows=274 loops=1)
> Index Cond: (((m)::text = 'Prod'::text) AND ((zl)::text =
> '200707311'::text))
> Filter: [...]
> Total runtime: 119.388 ms
>
> dlaczego tak sie dzieje ? Jak zoptymalizowac to pierwsze zapytanie ?
Zwróć uwagę na ilość zwracanych rekordów po zaaplikowaniu warunków. Postgres
widocznie uznał, że bardziej opłaca sie przeczytać mały index niż duży, mimo że
ten duży trochę bardziej ograniczyłby ilość wierszy, które należy odczytać z
tabeli.
Czy podjął słuszną decyzję... skasuj index tre_idx16 i sprawdź jaki będzie efekt.
--
P.M.
Melwin - 02-08-2007 00:04
Paweł Matejski napisał(a):
> A czemu nie napisałeś do jakiej bazy?
Sorry, PostgresSQL 8.1
> Jeśli chcesz pomocy od kogoś, czy nie lepiej jest jak najbardziej ułatwić
> zrozumienie Twojego problemu odpowiadającym - sformatować ładnie zapytanie.
Wiem ze wyglada nieczytelnie, ale przeciez jest na tyle
nieskomplikowane, ze da sie zrozumiec.
> Czy podjął słuszną decyzję... skasuj index tre_idx16 i sprawdź jaki będzie efekt.
Efekt podobny, czas nadal o wiele za duży
=?ISO-8859-2?Q?Pawe=B3_Matejski?= - 02-08-2007 00:04
Melwin wrote:
> Paweł Matejski napisał(a):
>> A czemu nie napisałeś do jakiej bazy?
>
> Sorry, PostgresSQL 8.1
>
>> Jeśli chcesz pomocy od kogoś, czy nie lepiej jest jak najbardziej ułatwić
>> zrozumienie Twojego problemu odpowiadającym - sformatować ładnie
>> zapytanie.
>
> Wiem ze wyglada nieczytelnie, ale przeciez jest na tyle
> nieskomplikowane, ze da sie zrozumiec.
Jak zrozumieć coś, czego nie da się przeczytać (skoro jest nieczytelne)? :)
Tu czepiam się słówek, ale chodzi o to, że na wgryzienie się w takie zapytanie
potrzeba czasu. A Tobie zależy przecież na tym, żebyśmy swój czas poświęcili na
rozwiązanie problemu, a nie rozgryzanie Twojego zapytania.
Mnie się, nie chciało w niego wgryzać, ale na szczęście (Twoje!) przeczytałem
Twój post do końca.
>> Czy podjął słuszną decyzję... skasuj index tre_idx16 i sprawdź jaki
>> będzie efekt.
>
> Efekt podobny, czas nadal o wiele za duży
No widzisz. Czyli postgres podjął słuszną decyzję.
1. Zamień warunki <>,!~~ na =, ~~ i załóż index na model.
2. Znormalizuj sobie strukturę.
Może któreś z tych pomogą, a 2 napewno nie zaszkodzi!
--
P.M.
=?ISO-8859-2?Q?Artur_Muszy=F1ski?= - 02-08-2007 00:04
Melwin pisze:
> Mam takie zapytanie:
>
> select grupa,model,dzal,dekor,opis,ntp,p,m,zl,sum(ile) as ile from tre
> where m='Prod' and (ZL IS NULL OR ZL ='200707311')
> and MODEL not LIKE 'ŁAWA%' and MODEL not LIKE 'STOLIK%' and MODEL not
> LIKE 'STÓŁ%' and MODEL <>'IMPERIUM' and MODEL <> 'MONTANA' and MODEL <>
> 'VENETO' and MODEL <> 'DOMINO' and MODEL <> 'SPARTA' and model<>'WARKA'
> and model<>'SPARTAN'
> and model not like 'CONF ŁAWA%' and model <> 'REKLAMACJA ŁAWA'
> and GRUPA<>'TAPI' and GRUPA<>'KUCH' and zam is not null
> group by grupa,model,dzal,dekor,opis,ntp,p,m,zl
> order by 1,2,3,4,5,6
> tre_idx12 na polach: m,zl,grupa,zam
grupa i zam w indeksie raczej na wiele ci się nie zdadzą, co do nulla,
to sądzę, że optymalizator po prostu nie lubi korzystać z indeksu w
takich sytuacjach, bo potencjalnie w tabeli będzie dużo wierszy
zawierające null.
Ja bym spróbował oszukać trochę i albo zamienić null na '' albo jeśli
nie chcesz ruszać tabeli to zamienić warunek na
COALESCE(ZL,'')='200707311' i załóż indeks na m,COALESCE(zl,'')
artur
> tre_idx16 na polach: m
>
> oba typu btree czyli powinny poprawnie indeksowac wartosci null.
>
>
>
>
>
> dlaczego tak sie dzieje ? Jak zoptymalizowac to pierwsze zapytanie ?
wloochacz - 03-08-2007 09:28
[ciach]
> grupa i zam w indeksie raczej na wiele ci się nie zdadzą, co do nulla,
> to sądzę, że optymalizator po prostu nie lubi korzystać z indeksu w
> takich sytuacjach, bo potencjalnie w tabeli będzie dużo wierszy
> zawierające null.
Chyba "lubienie" nie ma tu nic do rzeczy:
Cytat z dokumentacji:
"Constructs equivalent to combinations of these operators, such as
BETWEEN and IN, can also be implemented with a B-tree index search. (But
note that IS NULL is not equivalent to = and is not indexable.)"
http://www.postgresql.org/docs/8.2/i...xes-types.html
> Ja bym spróbował oszukać trochę i albo zamienić null na '' albo jeśli
> nie chcesz ruszać tabeli to zamienić warunek na
> COALESCE(ZL,'')='200707311' i załóż indeks na m,COALESCE(zl,'')
Pododbno COALESCE nic nie da ;-)
--
wloochacz
=?ISO-8859-2?Q?Pawe=B3_Matejski?= - 03-08-2007 09:28
wloochacz wrote:
> [ciach]
>> grupa i zam w indeksie raczej na wiele ci się nie zdadzą, co do nulla,
>> to sądzę, że optymalizator po prostu nie lubi korzystać z indeksu w
>> takich sytuacjach, bo potencjalnie w tabeli będzie dużo wierszy
>> zawierające null.
> Chyba "lubienie" nie ma tu nic do rzeczy:
> Cytat z dokumentacji:
> "Constructs equivalent to combinations of these operators, such as
> BETWEEN and IN, can also be implemented with a B-tree index search. (But
> note that IS NULL is not equivalent to = and is not indexable.)"
> http://www.postgresql.org/docs/8.2/i...xes-types.html
>
>> Ja bym spróbował oszukać trochę i albo zamienić null na '' albo jeśli
>> nie chcesz ruszać tabeli to zamienić warunek na
>> COALESCE(ZL,'')='200707311' i załóż indeks na m,COALESCE(zl,'')
> Pododbno COALESCE nic nie da ;-)
Da.
Za karę, że nie skojarzyłem tego problemu z indexowaniem null'i zrobiłem testy,
aby problem mi sie utrwalił!
madej=> \d b
Table "public.b"
Column | Type | Modifiers
--------+---------+-----------
id | integer | not null
id_a | integer |
madej=> SELECT count(*) from b;
count
---------
1009979
(1 row)
madej=> SELECT count(*) from b where id_a is null;
count
-------
9995
(1 row)
madej=> CREATE INDEX tt on b (id_a) where id_a is not null;
CREATE INDEX
madej=> CREATE INDEX ttc on b (coalesce(id_a,0)) where coalesce(id_a,0)=0;
CREATE INDEX
madej=> EXPLAIN analyze SELECT * from b where id_a = 100;
QUERY PLAN
------------------------------------------------------------------------------------------------------
Index Scan using tt on b (cost=0.00..3.01 rows=1 width=8) (actual
time=0.126..0.133 rows=1 loops=1)
Index Cond: (id_a = 100)
Total runtime: 0.210 ms
(3 rows)
madej=> EXPLAIN analyze SELECT * from b where coalesce(id_a,0) = 0 ;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on b (cost=30.67..5220.72 rows=5050 width=8) (actual
time=7.510..68.883 rows=9995 loops=1)
Recheck Cond: (COALESCE(id_a, 0) = 0)
-> Bitmap Index Scan on ttc (cost=0.00..30.67 rows=5050 width=0) (actual
time=7.439..7.439 rows=9995 loops=1)
Index Cond: (COALESCE(id_a, 0) = 0)
Total runtime: 89.388 ms
(5 rows)
Dlaczego indexy warunkowe:
madej=> SELECT pg_relation_size('tt'),pg_relation_size('ttc');
pg_relation_size | pg_relation_size
------------------+------------------
17965056 | 196608
Dla porównania wielkośc indeksów bez warunków:
madej=> SELECT pg_relation_size('tt'),pg_relation_size('ttc');
pg_relation_size | pg_relation_size
------------------+------------------
18145280 | 18145280
--
P.M.
=?ISO-8859-2?Q?Pawe=B3_Matejski?= - 03-08-2007 09:28
Po skierowaniu moich myśli na właściwe tory, szybko wpadłem na eleganckie
rozwiązane Twojego problemu.
Melwin wrote:
> Mam takie zapytanie:
>
> tre_idx12 na polach: m,zl,grupa,zam
> tre_idx16 na polach: m
Zamiast tre_idx12 utwórz dwa takie indexy:
CREATE INDEX tre_idx12zlnn on tre (m,zl,grupa,zam) where zl is not null;
CREATE INDEX tre_idx12zlnull on tre (m,grupa,zam) where zl is null;
Nie miałem danych, żeby przetestować, ale powinno zadziałać tak jak oczekujesz.
Oczywiście, przy założeniu, że rekordów z null nie jest zbyt wiele!
--
P.M.
Melwin - 03-08-2007 09:28
Paweł Matejski napisał(a):
> Zamiast tre_idx12 utwórz dwa takie indexy:
>
> CREATE INDEX tre_idx12zlnn on tre (m,zl,grupa,zam) where zl is not null;
> CREATE INDEX tre_idx12zlnull on tre (m,grupa,zam) where zl is null;
>
Namęczyłem się trochę ale załatwiłem sprawę inaczej, przerobiłem całe
oprogramowanie zeby zamiast null-a wpisywało '0' i teraz jest ok. Na
zwykłym indeksie na polu 'zl' jest juz w porzadku.
Mimo wszystko dzieki za pomoc.
--
Pozdrawiam
Przemysław Biernat
zanotowane.pldoc.pisz.plpdf.pisz.pleffulla.pev.pl
|
Oracle, SQL, PL/SQL. Jak =?ISO-8859-2?Q?napisa=E6_zapytanie=2C?==?ISO-8859-2?Q?_kt=F3re_zwr=F3ci_nazw=EA_atrybutu=2C_kt=F3reg o?==?ISO-8859-2?Q?_warto=B6ci_spe=B3niaj=B1_zadany_warunek?=
=?ISO-8859-2?Q?Narz=EAdzie_do_budowania_zapyta=F1_SQL=2C?==?I SO-8859-2?Q?_PL/PgSQL=2C_PL/SQL=2C_T-SQL?=
[ms sql] =?ISO-8859-2?Q?wy=B6wietlenie_pierwszych_5_rekord?==?ISO-8859-2?Q?=F3w_z_zapytania_=3F_odpowiednik_ROWNUM_w_o?== ?ISO-8859-2?Q?raclu_dla_MS_SQL=27a?=
mysql i mysql-front, problem
String line; if (line=="cos"){...}....problem
Problemy z =?ISO-8859-2?Q?instalacj=B1_PostgreSQL_na_syste?==?ISO-8859-2?Q?mach_Windows?=
[postgres] Problem z =?ISO-8859-2?Q?zmian=B1_struktury_i_z?==?ISO-8859-2?Q?ale=BFno=B6ciami=2E?=
[oracle] =?ISO-8859-2?Q?zmia=BFd=BFony_przez_problem=3A_za?==?ISO-8859-2?Q?pytanie_do_hierarchi?=
phpMyAdmin zadaje =?ISO-8859-2?Q?z=B3e_zapytania=2E=2E=2E_Dl?==?ISO-8859-2?Q?aczego=3F?=
[mysql 5.x] jak =?ISO-8859-2?Q?zrealizowa=E6_zapytanie=3F_cz?==?ISO-8859-2?Q?yli_podzapytanie_i_wi=EAcej_ni=BF_jeden_rz=B1? ==?ISO-8859-2?Q?d_wynik=F3w?=
zanotowane.pldoc.pisz.plpdf.pisz.plradioaktywni.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 |
|
