How to speed up Postgres product list retrieval query

How to speed up Postgres product list retrieval query

Shopping cart is running in Mono using ASP.NET MVC4 and uses Postgres 9.1
in Linux with 2 GB RAM. There are also some other databases, Apache and
some Mono MVC applications using PostgreSql.
Cart major query below returns one page of products.
Response time of query varies very mush is from 4 to 100 seconds and more.
100 seconds is too slow for users to wait for 25 products. How to speed it
up or find the slow part ? It tried explain analyze below but its output
is very long and I dont understand how to determine slow part.
Environment:
PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real
(Debian 4.4.5-8) 4.4.5, 64-bit
Query:
set search_path to firma19,public;
explain analyze select
toode.*,
(exists (select 1 from wishlist where
cartid=(('90104da8-c6fb-4540-87d6-bd0cdf0c82a8')) and
Toodeid=toode.toode)) as _lemmik,
coalesce( (select sum(kogus) from hetkesei where
hetkesei.toode=toode.toode
and osak in (select yksus from yksus1 where veebis) ) ,0) as _laoseis,
(select min(lopp) from dok join rid using (dokumnr)
where doktyyp='U' and not dok.taidetud and rid.toode=toode.toode
and taitmata>0
and lopp is not null) as _saabub,
(
select min(kuupaev) from dok join rid using (dokumnr)
where doktyyp in ('O','S') and not dok.eimuuda and
rid.toode=toode.toode and kogus>0
union
select min(kuupaev) from algsa
where algsa.toode=toode.toode and kogus>0
order by 1
limit 1
)::date as _EsimeneSaabumine,
coalesce(
( SELECT
coalesce( ROUNd( MyygiHind * (1 + 0/100),2),0)
FROM prpalk, (
(select
CASE
WHEN trim(lower(regexp_replace(tasusumma, E'\n\\*\\*.*', '', 'g')))
= 'iif( hinnamtr.hind#0, hinnamtr.hind, hkrid.hind )' THEN
case when HinnaMtr.hind is not null and
HinnaMtr.hind<>0 then HinnaMtr.hind else hkrid.hind
end
WHEN trim(lower(regexp_replace(tasusumma, E'\n\\*\\*.*', '', 'g')))
in ( 'hkrid.hind',
lower('hkrid.hind*iif(doktyyp!=[Y],1,(1+prpalk.kmprotsent/100))')
) THEN hkrid.hind
WHEN trim(lower(regexp_replace(tasusumma, E'\n\\*\\*.*',
'', 'g'))) in
('hinnamtr.hind',
lower('hinnamtr.hind*iif(doktyyp!=[Y],1,(1+prpalk.kmprotsent/100))')
) THEN hinnamtr.hind
WHEN hinnamtr.hinnak LIKE '%!%%' escape '!' THEN
(1-coalesce(nullif(substring(hinnamtr.hinnak,
'^[.0-9]*'),''),'0')::numeric/100 ) * toode.myygihind
ELSE
toode.myygihind
END
as MyygiHind,1 as jrk
from hinnamtr
left join hkrid on hinnamtr.hinnak= hkrid.hinnak and
hkrid.toode=toode.toode
LEFT JOIN hkpais ON hinnamtr.hinnak=hkpais.hinnak
LEFT JOIN klient ON klient.kood= ((''))
WHERE hinnamtr.toode=toode.toode and
( hinnamtr.klient is null or hinnamtr.klient=(('')) )
AND CURRENT_DATE>=hinnamtr.algus and (hinnamtr.lopp is
null or CURRENT_DATE<=lopp)
AND hinnamtr.hinnak is not null
AND ( hinnamtr.klliik is null or
hinnamtr.klliik=klient.liik )
order by prioriteet
limit 1
) UNION ALL (
select
CASE
WHEN trim(lower(regexp_replace(tasusumma, E'\n\\*\\*.*', '', 'g')))
= 'iif( hinnamtr.hind#0, hinnamtr.hind, hkrid.hind )' THEN
hkrid.hind
WHEN trim(lower(regexp_replace(tasusumma, E'\n\\*\\*.*', '', 'g')))
in ( 'hkrid.hind',
lower('hkrid.hind*iif(doktyyp!=[Y],1,(1+prpalk.kmprotsent/100))') )
THEN hkrid.hind
WHEN hkpais.hinnak LIKE '%!%%' escape '!' THEN
(1-coalesce(nullif(substring(hkpais.hinnak,
'^[.0-9]*'),''),'0')::numeric/100 ) * toode.myygihind
ELSE
toode.myygihind
END
,2
from klient
join hkpais on hkpais.hinnak=klient.hinnak and klient.kood=((''))
left join hkrid on hkrid.hinnak=hkpais.hinnak and hkrid.toode=toode.toode
where klient.hinnak is not null
) UNION ALL (
select toode.myygihind,3
)
) xx
order by xx.jrk
LIMIT 1
)
, toode.jaehind,0)::numeric as _poehind,
yksus5.nimetus as _varvus,
yksus7.nimetus as _KaubaMark,
coalesce( (select
trim(lower(regexp_replace(hkpais.tasusumma, E'\n\\*\\*.*', '', 'g'))) in
('hinnamtr.hind',
lower('hinnamtr.hind*iif(doktyyp!=[Y],1,(1+prpalk.kmprotsent/100))')
)
from hinnamtr
join hkpais on hinnamtr.hinnak=hkpais.hinnak
WHERE hinnamtr.toode=toode.toode and
( hinnamtr.klient is null or hinnamtr.klient=((NULL)) )
AND CURRENT_DATE>=hinnamtr.algus and (hinnamtr.lopp is null or
CURRENT_DATE<=lopp)
AND hinnamtr.hinnak is not null
order by prioriteet
limit 1), false)
as _hinnamtrhind,
coalesce( (exists ( select * from artomadu where artomaliik=((8))
and toode = toode.toode )), false ) as _Soodushind
from toode
left join yksus5 on toode.objekt5=yksus5.yksus
left join yksus7 on toode.objekt7=yksus7.yksus
where not toode.kassakeeld and (((NULL)) is null or
((NULL))=toode.toode ) and toode.grupp='L' and
( toode.seisund is null or toode.seisund in ('S','T') or
toode in ( select toode from hetkesei
where osak in (select yksus from yksus1 where veebis) and
kogus>0 )
)
and (((FALSE)) or toode.jaehind<>0) and
(((NULL)) is null or
case upper(((NULL)))
when 'VIIMASED 35 PÆ„EVA' then (
select min(kuupaev) from dok join rid using (dokumnr)
where doktyyp in ('O','S') and not dok.eimuuda and
rid.toode=toode.toode and kogus>0
union
select min(kuupaev) from algsa
where algsa.toode=toode.toode and kogus>0
order by 1
limit 1
) >= CURRENT_DATE-35
when 'VIIMASED 95 PÆ„EVA' then
(
select min(kuupaev) from dok join rid using (dokumnr)
where doktyyp in ('O','S') and not dok.eimuuda and
rid.toode=toode.toode and kogus>0
union
select min(kuupaev) from algsa
where algsa.toode=toode.toode and kogus>0
order by 1
limit 1
) >= CURRENT_DATE-95
when 'UUED TOOTED' then
to_date(toode.atimestamp, 'YYYYMMDDHH24MISS') >= CURRENT_DATE-95
when 'KAMPAANIATOOTED' then toode in (select toode from artomadu where
artomaliik=7)
when 'LEMMIKUD' then toode in (select Toodeid from wishlist where
cartid=(('90104da8-c6fb-4540-87d6-bd0cdf0c82a8')))
when 'MINU TOOTED' then
toode in (select toode from hinnamtr where klient=((NULL)) )
when 'AINULT LAOSOLEVAD' then
toode in ( select toode from hetkesei where
hetkesei.toode=toode.toode
and osak in (select yksus from yksus1 where veebis) and
kogus>0 )
else
toode.ribakood=((NULL))
or toode.nimetus ilike '%'||((NULL))||'%' escape '!'
or toode.toode ilike ((NULL))||'%' escape '!'
or yksus7.nimetus ilike '%'||((NULL))||'%' escape '!'
end
)
and
(((NULL)) is null or ((NULL))='' or ((NULL))=objekt5 ) and
(((NULL)) is null or ((NULL))='' or ((NULL))=objekt7 ) and
(((NULL)) is null or ((NULL))='' or ((NULL))=toode.liik ) and
(((NULL)) is null or ((NULL))='' or ((NULL))=toode.ryhm ) and
( toode in (select toode from ArtOmadu WHERE ((383)) is null or
artomaliik=((383)) ) )
and ( not ((FALSE))
or ( ((NULL))='HULGI' and toode.hulgihind is not null and
toode.hulgihind<>0 )
or (((NULL))<>'HULGI' AND toode.toode in (select toode from hkrid
where hinnak=((NULL))) )
or (toode.toode in (select toode from hinnamtr where klient=((''))))
)
order by toode.toode , toode.toode
offset ((175))
limit ((25))
output:
"Limit (cost=1049446.96..1049447.03 rows=25 width=2161) (actual
time=42820.857..42820.861 rows=25 loops=1)"
" -> Sort (cost=1049446.53..1049447.27 rows=299 width=2161) (actual
time=42820.836..42820.851 rows=200 loops=1)"
" Sort Key: toode.toode"
" Sort Method: quicksort Memory: 210kB"
" -> Hash Left Join (cost=308.43..1049434.23 rows=299 width=2161)
(actual time=3106.542..42818.053 rows=353 loops=1)"
" Hash Cond: (toode.objekt7 = yksus7.yksus)"
" -> Hash Left Join (cost=307.41..737.36 rows=299
width=2088) (actual time=6.609..20.982 rows=353 loops=1)"
" Hash Cond: (toode.objekt5 = yksus5.yksus)"
" -> Hash Semi Join (cost=306.36..735.18 rows=299
width=2017) (actual time=6.594..20.353 rows=353 loops=1)"
" Hash Cond: (toode.toode = firma19.artomadu.toode)"
" -> Seq Scan on toode (cost=136.40..549.56
rows=4415 width=2017) (actual time=0.035..15.524 rows=4402 loops=1)"
" Filter: ((NOT kassakeeld) AND (jaehind <>
0::numeric) AND (grupp = 'L'::bpchar) AND ((seisund IS NULL) OR (seisund =
ANY ('{S,T}'::bpchar[])) OR (hashed SubPlan 11)))"
" SubPlan 11"
" -> Hash Join (cost=10.42..135.51
rows=354 width=21) (actual time=0.034..2.811 rows=2750 loops=1)"
" Hash Cond: (firma19.hetkesei.osak
= firma19.yksus1.yksus)"
" -> Seq Scan on hetkesei
(cost=0.00..113.76 rows=2831 width=32) (actual time=0.010..1.429 rows=2819
loops=1)"
" Filter: (kogus > 0::numeric)"
" -> Hash (cost=10.41..10.41
rows=1 width=44) (actual time=0.016..0.016 rows=2 loops=1)"
" Buckets: 1024 Batches: 1
Memory Usage: 1kB"
" -> HashAggregate
(cost=10.40..10.41 rows=1 width=44) (actual time=0.014..0.014 rows=2
loops=1)"
" -> Seq Scan on
yksus1 (cost=0.00..10.40 rows=1 width=44) (actual time=0.006..0.007
rows=2 loops=1)"
" Filter: veebis"
" -> Hash (cost=165.55..165.55 rows=353
width=21) (actual time=2.247..2.247 rows=356 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage:
19kB"
" -> Seq Scan on artomadu
(cost=0.00..165.55 rows=353 width=21) (actual time=0.011..2.060 rows=356
loops=1)"
" Filter: (artomaliik = 383)"
" -> Hash (cost=1.02..1.02 rows=2 width=82) (actual
time=0.004..0.004 rows=2 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 1kB"
" -> Seq Scan on yksus5 (cost=0.00..1.02 rows=2
width=82) (actual time=0.001..0.002 rows=2 loops=1)"
" -> Hash (cost=1.01..1.01 rows=1 width=84) (actual
time=0.005..0.005 rows=5 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 1kB"
" -> Seq Scan on yksus7 (cost=0.00..1.01 rows=1
width=84) (actual time=0.002..0.004 rows=5 loops=1)"
" SubPlan 1"
" -> Seq Scan on wishlist (cost=0.00..14.95 rows=1
width=0) (never executed)"
" Filter: (((cartid)::text =
'90104da8-c6fb-4540-87d6-bd0cdf0c82a8'::text) AND (toodeid =
toode.toode))"
" SubPlan 2"
" -> Seq Scan on wishlist (cost=0.00..14.12 rows=2
width=84) (actual time=0.008..0.008 rows=0 loops=1)"
" Filter: ((cartid)::text =
'90104da8-c6fb-4540-87d6-bd0cdf0c82a8'::text)"
" SubPlan 3"
" -> Aggregate (cost=18.71..18.72 rows=1 width=4) (actual
time=0.078..0.078 rows=1 loops=353)"
" -> Nested Loop (cost=10.40..18.70 rows=1 width=4)
(actual time=0.068..0.075 rows=1 loops=353)"
" -> HashAggregate (cost=10.40..10.41 rows=1
width=44) (actual time=0.001..0.002 rows=2 loops=353)"
" -> Seq Scan on yksus1
(cost=0.00..10.40 rows=1 width=44) (actual time=0.003..0.004 rows=2
loops=1)"
" Filter: veebis"
" -> Index Scan using hetkesei_pkey on
hetkesei (cost=0.00..8.28 rows=1 width=15) (actual time=0.035..0.035
rows=0 loops=706)"
" Index Cond: ((osak =
firma19.yksus1.yksus) AND (toode = toode.toode))"
" SubPlan 4"
" -> Aggregate (cost=869.46..869.47 rows=1 width=4)
(actual time=113.922..113.922 rows=1 loops=353)"
" -> Nested Loop (cost=10.09..869.45 rows=1
width=4) (actual time=113.919..113.919 rows=0 loops=353)"
" -> Bitmap Heap Scan on rid
(cost=10.09..811.38 rows=7 width=4) (actual time=69.041..113.798 rows=15
loops=353)"
" Recheck Cond: (toode = toode.toode)"
" Filter: (taitmata > 0::numeric)"
" -> Bitmap Index Scan on rid_toode_idx
(cost=0.00..10.08 rows=213 width=0) (actual time=10.945..10.945 rows=509
loops=353)"
" Index Cond: (toode = toode.toode)"
" -> Index Scan using dok_pkey on dok
(cost=0.00..8.28 rows=1 width=8) (actual time=0.007..0.007 rows=0
loops=5149)"
" Index Cond: (dokumnr =
firma19.rid.dokumnr)"
" Filter: ((NOT taidetud) AND (lopp IS
NOT NULL) AND (doktyyp = 'U'::bpchar))"
" SubPlan 6"
" -> Limit (cost=2525.25..2525.25 rows=1 width=2) (actual
time=7.153..7.153 rows=1 loops=353)"
" -> Unique (cost=2525.25..2525.26 rows=2 width=2)
(actual time=7.152..7.152 rows=1 loops=353)"
" -> Sort (cost=2525.25..2525.25 rows=2
width=2) (actual time=7.148..7.148 rows=1 loops=353)"
" Sort Key: (min(firma19.dok.kuupaev))"
" Sort Method: quicksort Memory: 25kB"
" -> Append (cost=2178.87..2525.24
rows=2 width=2) (actual time=3.172..7.135 rows=2 loops=353)"
" -> Aggregate
(cost=2178.87..2178.88 rows=1 width=4) (actual time=3.171..3.171 rows=1
loops=353)"
" -> Nested Loop
(cost=10.13..2178.80 rows=27 width=4) (actual time=0.278..3.152 rows=77
loops=353)"
" -> Bitmap Heap Scan
on rid (cost=10.13..811.43 rows=187 width=4) (actual time=0.259..1.042
rows=462 loops=353)"
" Recheck Cond:
(toode = toode.toode)"
" Filter: (kogus
> 0::numeric)"
" -> Bitmap
Index Scan on rid_toode_idx (cost=0.00..10.08 rows=213 width=0) (actual
time=0.168..0.168 rows=509 loops=353)"
" Index
Cond: (toode = toode.toode)"
" -> Index Scan using
dok_pkey on dok (cost=0.00..7.30 rows=1 width=8) (actual
time=0.004..0.004 rows=0 loops=163234)"
" Index Cond:
(dokumnr = firma19.rid.dokumnr)"
" Filter: ((NOT
eimuuda) AND (doktyyp = ANY ('{O,S}'::bpchar[])))"
" -> Result (cost=346.32..346.33
rows=1 width=0) (actual time=3.961..3.961 rows=1 loops=353)"
" InitPlan 5 (returns $4)"
" -> Limit
(cost=0.00..346.32 rows=1 width=4) (actual time=3.958..3.958 rows=1
loops=353)"
" -> Index Scan
using algsa_kuupaev_idx on algsa (cost=0.00..2077.95 rows=6 width=4)
(actual time=3.956..3.956 rows=1 loops=353)"
" Index Cond:
(kuupaev IS NOT NULL)"
" Filter:
((kogus > 0::numeric) AND (toode = toode.toode))"
" SubPlan 7"
" -> Limit (cost=55.28..55.28 rows=1 width=36) (actual
time=0.055..0.055 rows=1 loops=353)"
" -> Sort (cost=55.28..55.30 rows=9 width=36)
(actual time=0.053..0.053 rows=1 loops=353)"
" Sort Key: "*SELECT* 1".jrk"
" Sort Method: quicksort Memory: 25kB"
" -> Nested Loop (cost=32.21..55.23 rows=9
width=36) (actual time=0.041..0.042 rows=1 loops=353)"
" -> Seq Scan on prpalk
(cost=0.00..3.03 rows=3 width=0) (actual time=0.004..0.004 rows=1
loops=353)"
" -> Materialize (cost=32.21..52.05
rows=3 width=36) (actual time=0.032..0.033 rows=1 loops=353)"
" -> Append (cost=32.21..52.04
rows=3 width=36) (actual time=0.029..0.030 rows=1 loops=353)"
" -> Subquery Scan on
"*SELECT* 1" (cost=32.21..32.23 rows=1 width=36) (actual
time=0.018..0.018 rows=0 loops=353)"
" -> Limit
(cost=32.21..32.22 rows=1 width=141) (actual time=0.017..0.017 rows=0
loops=353)"
" -> Sort
(cost=32.21..32.22 rows=1 width=141) (actual time=0.016..0.016 rows=0
loops=353)"
" Sort Key:
firma19.hinnamtr.prioriteet"
" Sort
Method: quicksort Memory: 25kB"
" ->
Nested Loop Left Join (cost=0.00..32.20 rows=1 width=141) (actual
time=0.012..0.012 rows=0 loops=353)"
"
Filter: ((firma19.hinnamtr.klliik IS NULL) OR (firma19.hinnamtr.klliik =
firma19.klient.liik))"
" ->
Nested Loop Left Join (cost=0.00..23.87 rows=1 width=185) (actual
time=0.012..0.012 rows=0 loops=353)"
"
-> Nested Loop Left Join (cost=0.00..15.58 rows=1 width=179) (actual
time=0.011..0.011 rows=0 loops=353)"
"
Join Filter: (firma19.hinnamtr.hinnak = firma19.hkpais.hinnak)"
"
-> Seq Scan on hinnamtr (cost=0.00..12.40 rows=1 width=98)
(actual time=0.009..0.009 rows=0 loops=353)"
"
Filter: ((hinnak IS NOT NULL) AND ((klient IS NULL) OR
(klient = ''::bpchar)) AND (toode = toode.toode) AND (('now'::text)::date
>= algus) AND ((lopp IS NULL) OR (('now (...)"
"
-> Seq Scan on hkpais (cost=0.00..3.08 rows=8 width=87) (never
executed)"
"
-> Index Scan using hkrid_hinnak_toode_unique on hkrid
(cost=0.00..8.27 rows=1 width=12) (never executed)"
"
Index Cond: ((firma19.hinnamtr.hinnak = hinnak) AND (toode =
toode.toode))"
" ->
Index Scan using klient_pkey on klient (cost=0.00..8.27 rows=1 width=11)
(never executed)"
"
Index Cond: (kood = ''::bpchar)"
" -> Nested Loop Left Join
(cost=0.00..19.78 rows=1 width=93) (actual time=0.008..0.008 rows=0
loops=353)"
" -> Nested Loop
(cost=0.00..11.45 rows=1 width=87) (actual time=0.008..0.008 rows=0
loops=353)"
" Join Filter:
(firma19.klient.hinnak = firma19.hkpais.hinnak)"
" -> Index Scan
using klient_pkey on klient (cost=0.00..8.27 rows=1 width=6) (actual
time=0.007..0.007 rows=0 loops=353)"
" Index
Cond: (kood = ''::bpchar)"
" Filter:
(hinnak IS NOT NULL)"
" -> Seq Scan on
hkpais (cost=0.00..3.08 rows=8 width=87) (never executed)"
" -> Index Scan using
hkrid_hinnak_toode_unique on hkrid (cost=0.00..8.27 rows=1 width=12)
(never executed)"
" Index Cond:
((hinnak = firma19.hkpais.hinnak) AND (toode = toode.toode))"
" -> Result
(cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1
loops=353)"
" SubPlan 8"
" -> Limit (cost=15.40..15.40 rows=1 width=93) (actual
time=0.009..0.009 rows=0 loops=353)"
" -> Sort (cost=15.40..15.40 rows=1 width=93)
(actual time=0.008..0.008 rows=0 loops=353)"
" Sort Key: firma19.hinnamtr.prioriteet"
" Sort Method: quicksort Memory: 25kB"
" -> Nested Loop (cost=0.00..15.39 rows=1
width=93) (actual time=0.005..0.005 rows=0 loops=353)"
" Join Filter: (firma19.hinnamtr.hinnak =
firma19.hkpais.hinnak)"
" -> Seq Scan on hinnamtr
(cost=0.00..12.20 rows=1 width=36) (actual time=0.004..0.004 rows=0
loops=353)"
" Filter: (((klient IS NULL) OR
NULL::boolean) AND (hinnak IS NOT NULL) AND (toode = toode.toode) AND
(('now'::text)::date >= algus) AND ((lopp IS NULL) OR (('now'::text)::date
<= lopp)))"
" -> Seq Scan on hkpais
(cost=0.00..3.08 rows=8 width=87) (never executed)"
" SubPlan 9"
" -> Index Scan using artomadu_pkey on artomadu
(cost=0.00..8.27 rows=1 width=0) (never executed)"
" Index Cond: ((toode = toode.toode) AND (artomaliik
= 8))"
" SubPlan 10"
" -> Seq Scan on artomadu (cost=0.00..165.55 rows=10
width=21) (actual time=1.508..1.508 rows=0 loops=1)"
" Filter: (artomaliik = 8)"
"Total runtime: 42821.807 ms"