help - Mailing list pgsql-sql

From Tony Simbine
Subject help
Date
Msg-id 3EE88B1F.2050003@mvweb.de
Whole thread Raw
Responses Re: help
List pgsql-sql
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)





pgsql-sql by date:

Previous
From: Robert Treat
Date:
Subject: control structures in plpgsql
Next
From: Stephan Szabo
Date:
Subject: Re: control structures in plpgsql