Thread: help
hello,
I've a query which needs too many time ca. 12-15 sec.
how can i get a better perfomance?
my table have less than 2300 rows.
thanks in advance
tony
explain select o.id from ioobeject o,dist_vertron v where
macro_lid=1123 and (o.id=v.id) and (o.deleted<>'1') and
(o.status='activo') order by o.ort; QUERY
PLAN
---------------------------------------------------------------------------------------------------------------------------------
-------------- Sort (cost=790.49..791.63 rows=457 width=66) Sort Key: o.ort -> Hash Join (cost=228.88..770.31
rows=457width=66) Hash Cond: ("outer".id = "inner".id) -> Append (cost=0.00..502.35 rows=3337
width=4) -> Seq Scan on dist_vertron v (cost=0.00..0.00 rows=1
width=4) -> Seq Scan on disposicao v (cost=0.00..136.87
rows=987 width=4) -> Seq Scan on oponente v (cost=0.00..0.00 rows=1 width=4) -> Seq Scan
onnovinho v (cost=0.00..5.14 rows=14 width=4) -> Seq Scan on colagem_livre v (cost=0.00..194.69
rows=1369 width=4) -> Seq Scan on jardim_contribuicao v
(cost=0.00..149.08 rows=808 width=4) -> Seq Scan on jardim_comardia v (cost=0.00..16.57
rows=157 width=4) -> Hash (cost=228.81..228.81 rows=27 width=62) -> Append (cost=0.00..228.81
rows=27width=62) -> Seq Scan on ioobeject o (cost=0.00..0.00
rows=1 width=62) Filter: ((macro_lid = 1123) AND (deleted <>
'1'::bpchar) AND (status = 'activo'::character varying)) -> Index Scan using
iwohnung_macro_lid_indexon
iwohnung o (cost=0.00..28.71 rows=3 width=17) Index Cond: (macro_lid = 1123)
Filter: ((deleted <> '1'::bpchar) AND
(status = 'activo'::character varying)) -> Index Scan using
izoologicowohnung_macro_lid_index on izoologicowohnung o
(cost=0.00..14.70 rows=1 width=19) Index Cond: (macro_lid = 1123)
Filter:((deleted <> '1'::bpchar) AND
(status = 'activo'::character varying)) -> Index Scan using icolagem_macro_lid_index on
icasamento o (cost=0.00..21.06 rows=2 width=18) Index Cond: (macro_lid = 1123)
Filter: ((deleted <> '1'::bpchar) AND
(status = 'activo'::character varying)) -> Index Scan using
iwohn_geschaefts_colagem_macro_lid__index on iwohn_geschaefts_casamento
o (cost=0.00..7.80 row
s=1 width=16) Index Cond: (macro_lid = 1123) Filter: ((deleted <>
'1'::bpchar)AND
(status = 'activo'::character varying)) -> Index Scan using
ialinhadocolagem_macro_lid_index on ialinhadocasamento o
(cost=0.00..8.30 rows=1 width=18) Index Cond: (macro_lid = 1123)
Filter:((deleted <> '1'::bpchar) AND
(status = 'activo'::character varying)) -> Index Scan using
idobro_colagem_macro_lid_index on idobro_casamento o (cost=0.00..6.08
rows=1 width=17) Index Cond: (macro_lid = 1123) Filter: ((deleted
<>'1'::bpchar) AND
(status = 'activo'::character varying)) -> Index Scan using ibauernhof_mediador_index on
ibauernhof o (cost=0.00..8.53 rows=1 width=18) Index Cond: (macro_lid = 1123)
Filter: ((deleted <> '1'::bpchar) AND
(status = 'activo'::character varying)) -> Index Scan using
imehrfamcolagem_mediador_index on imehrfamcasamento o (cost=0.00..11.49
rows=1 width=17) Index Cond: (macro_lid = 1123) Filter: ((deleted
<>'1'::bpchar) AND
(status = 'activo'::character varying)) -> Index Scan using
izoologicocolagem_macro_lid_index on izoologicocasamento o
(cost=0.00..8.03 rows=1 width=17) Index Cond: (macro_lid = 1123)
Filter:((deleted <> '1'::bpchar) AND
(status = 'activo'::character varying)) -> Index Scan using
igartenbungalow_macro_lid_index on igartenbungalow o (cost=0.00..9.20
rows=1 width=19) Index Cond: (macro_lid = 1123) Filter: ((deleted
<>'1'::bpchar) AND
(status = 'activo'::character varying)) -> Index Scan using iescritor_mediador_index on
iescritor o (cost=0.00..7.16 rows=1 width=17) Index Cond: (macro_lid = 1123)
Filter: ((deleted <> '1'::bpchar) AND
(status = 'activo'::character varying)) -> Index Scan using ivilla_macro_lid_index on
ivilla o (cost=0.00..7.91 rows=1 width=16) Index Cond: (macro_lid = 1123)
Filter: ((deleted <> '1'::bpchar) AND
(status = 'activo'::character varying)) -> Index Scan using igutscolagem_macro_lid_index
on igutscasamento o (cost=0.00..7.97 rows=1 width=18) Index Cond: (macro_lid = 1123)
Filter: ((deleted <> '1'::bpchar) AND
(status = 'activo'::character varying)) -> Seq Scan on ischloss o (cost=0.00..0.00
rows=1 width=62) Filter: ((macro_lid = 1123) AND (deleted <>
'1'::bpchar) AND (status = 'activo'::character varying)) -> Seq Scan on isonstige o
(cost=0.00..0.00
rows=1 width=62) Filter: ((macro_lid = 1123) AND (deleted <>
'1'::bpchar) AND (status = 'activo'::character varying)) -> Index Scan using
idobro_casamentometade_macro_lid_index on idobro_casamentometade o
(cost=0.00..8.40 rows=1 wid
th=17) Index Cond: (macro_lid = 1123) Filter: ((deleted <>
'1'::bpchar)AND
(status = 'activo'::character varying)) -> Index Scan using igrundstueck_macro_lid_index
on igrundstueck o (cost=0.00..17.02 rows=2 width=17) Index Cond: (macro_lid = 1123)
Filter: ((deleted <> '1'::bpchar) AND
(status = 'activo'::character varying)) -> Index Scan using iloft_macro_lid_index on
iloft o (cost=0.00..5.99 rows=1 width=15) Index Cond: (macro_lid = 1123)
Filter: ((deleted <> '1'::bpchar) AND
(status = 'activo'::character varying)) -> Index Scan using
ispezialimmobilie_macro_lid_index on ispezialimmobilie o
(cost=0.00..9.90 rows=1 width
=17) Index Cond: (macro_lid = 1123) Filter: ((deleted <>
'1'::bpchar)AND
(status = 'activo'::character varying)) -> Index Scan using ilager_macro_lid_index on
ilager o (cost=0.00..16.50 rows=1 width=16) Index Cond: (macro_lid = 1123)
Filter: ((deleted <> '1'::bpchar) AND
(status = 'activo'::character varying)) -> Index Scan using icomardiaraum_macro_lid_index
on icomardiaraum o (cost=0.00..8.51 rows=1 width=19) Index Cond: (macro_lid = 1123)
Filter: ((deleted <> '1'::bpchar) AND
(status = 'activo'::character varying)) -> Index Scan using izimmer_macro_lid_index on
izimmer o (cost=0.00..15.55 rows=1 width=20) Index Cond: (macro_lid = 1123)
Filter: ((deleted <> '1'::bpchar) AND
(status = 'activo'::character varying))
(80 rows)
Tony, > I've a query which needs too many time ca. 12-15 sec. > how can i get a better perfomance? First of all, please take this to the PGSQL-PERFORMANCE list. Second, see this web page: http://techdocs.postgresql.org/guides/SlowQueryPostingGuidelines > explain select o.id from ioobeject o,dist_vertron v where > macro_lid=1123 and (o.id=v.id) and (o.deleted<>'1') and > (o.status='activo') order by o.ort; Third, from your explain, ioobject and dist_vertron are obviously somewhat complex views. We need those view definitions, possibly plus schema for the underlying tables (including indexes), or we can't help you. -- Josh Berkus Aglio Database Solutions San Francisco
Josh, thanks for your help. Josh Berkus wrote: > Tony, > > >>I've a query which needs too many time ca. 12-15 sec. >>how can i get a better perfomance? > > > First of all, please take this to the PGSQL-PERFORMANCE list. > > Second, see this web page: > http://techdocs.postgresql.org/guides/SlowQueryPostingGuidelines i read some tips on the above pages and my database and all my queries run very well. i've optimized the queriey from where ... IN (select ..) to EXISTS and I've done vacumdb full. my requests last between 0.009 to 0.2 sec compared with 12-15 sec. i'm very happy. thanks very much. tony > > >>explain select o.id from ioobeject o,dist_vertron v where >>macro_lid=1123 and (o.id=v.id) and (o.deleted<>'1') and >>(o.status='activo') order by o.ort; > > > Third, from your explain, ioobject and dist_vertron are obviously somewhat > complex views. We need those view definitions, possibly plus schema for the > underlying tables (including indexes), or we can't help you. >