DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=JesZEwEpZBwQJ4oq5dv3vMxPbx0OIDDx/VnhJ1zT62x1F2Q0TNfainprsuEpwigBw955469gOWNYf9A8FHRVQLyh5ZqWDTWuMpyX0h1GZ3Po7alPSHExvkKSiOswIEEgBgbu81ZK7U9uQIasNxhkiBPvfsY67UWvcdJtmnGt3c0=
Message-ID: <194f62550605100449p2d200fc3pfe501da895cfcc00@mail.gmail.com>
Date: Wed, 10 May 2006 13:49:41 +0200
From: "Clemens Eisserer" <linuxhippy@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Question about explain-command...
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hello,

I just discovered the explain command and well ... have some (for you
of course very stupid) questions.

I do a quite large (for my taste) join, the query looks like the following:
SELECT DISTINCT customer.email AS cemail, customer.key AS ckey,
customer.anrede AS canrede, customer.strasse AS cstrasse, customer.plz
AS cplz, customer.ort AS cort, customer.vorname AS cvorname,
customer.nachname AS cnachname , custtype.name AS tname, customer.land
AS cland, customer.datanotvalid AS cdatanvalid FROM customer LEFT JOIN
sells ON customer.key=3Dsells.custid LEFT JOIN goods ON
sells.goodsid=3Dgoods.key LEFT JOIN custtype ON
customer.custgroup=3Dcusttype.key LEFT JOIN prodtype ON
prodtype.key=3Dgoods.prodgroup WHERE customer.nachname LIKE  '%name%';

All primary keys are indixed, and this is what explain tells me:
 Unique  (cost=3D15.67..16.69 rows=3D34 width=3D115)
   ->  Sort  (cost=3D15.67..15.75 rows=3D34 width=3D115)
         Sort Key: customer.email, customer."key", customer.anrede, custome=
r.str
asse, customer.plz, customer.ort, customer.vorname, customer.nachname, cust=
type.
name, customer.land, customer.datanotvalid
         ->  Hash Left Join  (cost=3D6.16..14.80 rows=3D34 width=3D115)
               Hash Cond: ("outer".prodgroup =3D "inner"."key")
               ->  Hash Left Join  (cost=3D4.97..13.10 rows=3D34 width=3D11=
9)
                     Hash Cond: ("outer".custgroup =3D "inner"."key")
                     ->  Hash Left Join  (cost=3D3.88..11.49 rows=3D34 widt=
h=3D111)
                           Hash Cond: ("outer".goodsid =3D "inner"."key")
                           ->  Hash Left Join  (cost=3D1.98..9.08
rows=3D34 width=3D11                                1)
                                 Hash Cond: ("outer"."key" =3D "inner".cust=
id)
                                 ->  Seq Scan on customer=20
(cost=3D0.00..6.10 rows                                =3D34 width=3D107)
                                       Filter: ((nachname)::text ~~
'%au%'::text                                )
                                 ->  Hash  (cost=3D1.78..1.78 rows=3D78 wid=
th=3D8)
                                       ->  Seq Scan on sells=20
(cost=3D0.00..1.78 r                                ows=3D78 width=3D8)
                           ->  Hash  (cost=3D1.72..1.72 rows=3D72 width=3D8=
)
                                 ->  Seq Scan on goods=20
(cost=3D0.00..1.72 rows=3D72                                 width=3D8)
                     ->  Hash  (cost=3D1.08..1.08 rows=3D8 width=3D16)
                           ->  Seq Scan on custtype  (cost=3D0.00..1.08
rows=3D8 wid                                th=3D16)
               ->  Hash  (cost=3D1.15..1.15 rows=3D15 width=3D4)
                     ->  Seq Scan on prodtype  (cost=3D0.00..1.15 rows=3D15=
 width=3D4)


What does the hash-lines mean, does that mean my query does not use
the indices at all?
Why are some table-names and some column-names surrounded by ' " '?
Are they threated as text-columns?
I have to admit that the tables are just filled with test-data so the
analyzer may take just a very simple way since almost no data is in...

lg Clemens