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=457 width=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 on novinho 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=27 width=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_index on 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)
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
