pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: David M. Richter <d.richter@dkfz.de>
To: pgsql-sql@postgresql.org
Subject: queryoptimizer force index use
Date: Wed, 19 Dec 2001 20:11:42 +0100
Message-ID: <3C20E66E.50D67B2B@dkfz.de> (raw)

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

Attachments:

d.richter.vcf (text/x-vcard, 497B) download 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

Message-ID: <3C20E66E.50D67B2B@dkfz.de>
Permalink:  ../3C20E66E.50D67B2B@dkfz.de/
Also on:    postgresql.org/message-id/3C20E66E.50D67B2B@dkfz.de

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: d.richter@dkfz.de
  Subject: Re: queryoptimizer force index use
  In-Reply-To: <3C20E66E.50D67B2B@dkfz.de>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox