Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fBJJe1m33255 for ; Wed, 19 Dec 2001 14:40:02 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id fBJJClV11576 for pgsql-sql@postgresql.org; Wed, 19 Dec 2001 14:12:47 -0500 (EST) (envelope-from news) From: "David M. Richter" X-Newsgroups: comp.databases.postgresql.sql Subject: queryoptimizer force index use Date: Wed, 19 Dec 2001 20:11:42 +0100 Organization: German Cancer Research Center Lines: 156 Message-ID: <3C20E66E.50D67B2B@dkfz.de> Mime-Version: 1.0 Content-Type: multipart/mixed; boundary="------------B2823ACC7F219AC36B7C8B48" X-Complaints-To: news@hub.org X-Mailer: Mozilla 4.7 [en] (X11; I; IRIX64 6.4 IP27) X-Accept-Language: en To: pgsql-sql@postgresql.org X-Archive-Number: 200112/208 X-Sequence-Number: 6031 This is a multi-part message in MIME format. --------------B2823ACC7F219AC36B7C8B48 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Hello! We have postgres 6.5.3 . We will update to 7.1.3on march 2002, because the database is in heavy use. Every night we make a vacuum and delete and recreate the indexes. We 've got that query, wich is very slow: chili=> explain SELECT study.*,series.*,image.* FROM study, series, image, relstudy_series000 r0, relseries_image000 r1 WHERE (study.chiliOID='0xaec1c13f.8271.1000122247.1') AND (study.chiliOID=r0.parentOID AND series.chiliOID=r0.childOID) and (series.chiliOID=r1.parentOID and image.chiliOID = r1.childOID) AND image.imageType ~* 'LOCALIZER'; NOTICE: QUERY PLAN: Hash Join (cost=488316.53 rows=19345 width=528) -> Nested Loop (cost=20999.69 rows=265624 width=360) -> Nested Loop (cost=906.51 rows=524 width=336) -> Nested Loop (cost=35.26 rows=425 width=224) -> Index Scan using indstudy_oid on study (cost=2.05 rows=2 width=200) -> Index Scan using indstudy_series_parent on relstudy_series000 r0 (cost=16.61 rows=263398 width=24) -> Index Scan using indseries_oid on series (cost=2.05 rows=263399 width=112) -> Index Scan using indseries_image_parent on relseries_image000 r1 (cost=38.35 rows=4780608 width=24) -> Hash (cost=406837.75 rows=347439 width=168) -> Seq Scan on image (cost=406837.75 rows=347439 width=168) ----Why is here a hash Join??????? and a Seq scan on image, the index indimage_oid is still existing. Why is the index indimage_oid not used? ----But without the last constraint the Query is very faster!!! chili=> explain SELECT study.*,series.*,image.* FROM chili-> study,series,image,relstudy_series000 r0, relseries_image000 r1 WHERE chili-> (study.chiliOID='0xaec1c13f.8271.1000122247.1') AND chili-> (study.chiliOID=r0.parentOID AND series.chiliOID=r0.childOID) and chili-> (series.chiliOID=r1.parentOID and image.chiliOID = r1.childOID) ; NOTICE: QUERY PLAN: Nested Loop (cost=565528.88 rows=265624 width=528) -> Nested Loop (cost=20999.69 rows=265624 width=360) -> Nested Loop (cost=906.51 rows=524 width=336) -> Nested Loop (cost=35.26 rows=425 width=224) -> Index Scan using indstudy_oid on study (cost=2.05 rows=2 width=200) -> Index Scan using indstudy_series_parent on relstudy_series000 r0 (cost=16.61 rows=263398 width=24) -> Index Scan using indseries_oid on series (cost=2.05 rows=263399 width=112) -> Index Scan using indseries_image_parent on relseries_image000 r1 (cost=38.35 rows=4780608 width=24) -> Index Scan using indimage_oid on image (cost=2.05 rows=4770811 width=168) EXPLAIN ---------------------------------------------- So we decided to dump that database. we redumped the database on an other Server and created the indexes. And now : the 2 Queries have quite the same runtime behavior. pacs=# explain SELECT study.*,series.*,image.* FROM pacs-# study,series,image,relstudy_series000 r0, relseries_image000 r1 WHERE pacs-# (study.chiliOID='0xaec1c13f.8271.1000122247.1') AND pacs-# (study.chiliOID=r0.parentOID AND series.chiliOID=r0.childOID) and pacs-# (series.chiliOID=r1.parentOID and image.chiliOID = r1.childOID) ; NOTICE: QUERY PLAN: Nested Loop (cost=579.92 rows=446 width=528) -> Nested Loop (cost=61.50 rows=255 width=360) -> Nested Loop (cost=32.80 rows=14 width=336) -> Nested Loop (cost=6.15 rows=13 width=224) -> Index Scan using indstudy_oid on study (cost=2.05 rows=2 width=200) -> Index Scan using indstudy_series_parent on relstudy_series000 r0 (cost=2.05 rows=262709 width=24) -> Index Scan using indseries_oid on series (cost=2.05 rows=262701 width=112) -> Index Scan using indseries_image_parent on relseries_image000 r1 (cost=2.05 rows=4768420 width=24) -> Index Scan using indimage_oid on image (cost=2.03 rows=8333843 width=168) EXPLAIN pacs=# explain SELECT study.*,series.*,image.* FROM study, series, image, relstudy_series000 r0, relseries_image000 r1 WHERE (study.chiliO ID='0xaec1c13f.8271.1000122247.1') AND (study.chiliOID=r0.parentOID AND series.chiliOID=r0.childOID) and (series.chiliOID=r1.parentOID and image.chiliOID = r1.childOID) AND image.imageType ~* 'LOCALIZER'; NOTICE: QUERY PLAN: Nested Loop (cost=579.92 rows=1 width=528) -> Nested Loop (cost=61.50 rows=255 width=360) -> Nested Loop (cost=32.80 rows=14 width=336) -> Nested Loop (cost=6.15 rows=13 width=224) -> Index Scan using indstudy_oid on study (cost=2.05 rows=2 width=200) -> Index Scan using indstudy_series_parent on relstudy_series000 r0 (cost=2.05 rows=262709 width=24) -> Index Scan using indseries_oid on series (cost=2.05 rows=262701 width=112) -> Index Scan using indseries_image_parent on relseries_image000 r1 (cost=2.05 rows=4768420 width=24) -> Index Scan using indimage_oid on image (cost=2.03 rows=2 width=168) '######################### We deleted and created the indexes on the first database. made a vacuum analyze. nothing happens. Both postgresinstallations are made with default options. Whats wrong? Have anyone of You experienced the same occasion of that.? What can I do , to get the behavior of the second database, to the first? Why is the behavior so different?? Greetings and thanks in advance David --------------B2823ACC7F219AC36B7C8B48 Content-Type: text/x-vcard; charset=us-ascii; name="d.richter.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for David M. Richter Content-Disposition: attachment; filename="d.richter.vcf" begin:vcard url;quoted-printable:http://mbi.DKFZ-Heidelberg.de/=0D=0A n:M. Richter;David x-mozilla-html:FALSE org:Deutsches Krebsforschungszentrum/German Cancer Research Center;Division Medizinische und Biologische Informatik version:2.1 email;internet:d.richter@dkfz-heidelberg.de adr;quoted-printable:;;Im Neuenheimer Feld 280 url: http://mbi.DKFZ-Heidelberg.de/=0D=0AD-69120 Heidelberg, Germany ;Heidelberg;Germany;; x-mozilla-cpt:;-17024 fn:David M. Richter end:vcard --------------B2823ACC7F219AC36B7C8B48--