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=sells.custid LEFT JOIN goods ON
sells.goodsid=goods.key LEFT JOIN custtype ON
customer.custgroup=custtype.key LEFT JOIN prodtype ON
prodtype.key=goods.prodgroup WHERE customer.nachname LIKE '%name%';
All primary keys are indixed, and this is what explain tells me:
Unique (cost=15.67..16.69 rows=34 width=115)
-> Sort (cost=15.67..15.75 rows=34 width=115)
Sort Key: customer.email, customer."key", customer.anrede, customer.str
asse, customer.plz, customer.ort, customer.vorname, customer.nachname, custtype.
name, customer.land, customer.datanotvalid
-> Hash Left Join (cost=6.16..14.80 rows=34 width=115)
Hash Cond: ("outer".prodgroup = "inner"."key")
-> Hash Left Join (cost=4.97..13.10 rows=34 width=119)
Hash Cond: ("outer".custgroup = "inner"."key")
-> Hash Left Join (cost=3.88..11.49 rows=34 width=111)
Hash Cond: ("outer".goodsid = "inner"."key")
-> Hash Left Join (cost=1.98..9.08
rows=34 width=11 1)
Hash Cond: ("outer"."key" = "inner".custid)
-> Seq Scan on customer
(cost=0.00..6.10 rows =34 width=107)
Filter: ((nachname)::text ~~
'%au%'::text )
-> Hash (cost=1.78..1.78 rows=78 width=8)
-> Seq Scan on sells
(cost=0.00..1.78 r ows=78 width=8)
-> Hash (cost=1.72..1.72 rows=72 width=8)
-> Seq Scan on goods
(cost=0.00..1.72 rows=72 width=8)
-> Hash (cost=1.08..1.08 rows=8 width=16)
-> Seq Scan on custtype (cost=0.00..1.08
rows=8 wid th=16)
-> Hash (cost=1.15..1.15 rows=15 width=4)
-> Seq Scan on prodtype (cost=0.00..1.15 rows=15 width=4)
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
