X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id 3C97B30E7B5 for ; Thu, 12 Jun 2003 14:16:06 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 74308-09 for ; Thu, 12 Jun 2003 11:15:55 -0300 (ADT) Received: from manica.mvweb.de (unknown [212.184.88.123]) by svr1.postgresql.org (Postfix) with SMTP id E69AB30E429 for ; Thu, 12 Jun 2003 11:15:53 -0300 (ADT) Received: (qmail 6659 invoked by uid 104); 12 Jun 2003 14:36:07 -0000 Received: from jantos-work@mvweb.de by manica.mvweb.de by uid 101 with qmail-scanner-1.16 (sweep: 2.14/3.68. clamscan: 0.54. spamassassin: 2.52. Clear:. Processed in 0.364192 secs); 12 Jun 2003 14:36:07 -0000 X-Qmail-Scanner-Mail-From: jantos-work@mvweb.de via manica.mvweb.de X-Qmail-Scanner: 1.16 (Clear:. Processed in 0.364192 secs) Received: from mvwebsn-dmz.mvweb.de (HELO mvweb.de) (212.184.88.121) by manica.mvweb.de with SMTP; 12 Jun 2003 14:36:06 -0000 Message-ID: <3EE88B1F.2050003@mvweb.de> Date: Thu, 12 Jun 2003 16:15:59 +0200 From: Tony Simbine User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0.1) Gecko/20021003 X-Accept-Language: de, pt, en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: help Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Archive-Number: 200306/113 X-Sequence-Number: 13871 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=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)