Question about explain-command... - Mailing list pgsql-performance

From Clemens Eisserer
Subject Question about explain-command...
Date
Msg-id 194f62550605100449p2d200fc3pfe501da895cfcc00@mail.gmail.com
Whole thread Raw
Responses Re: Question about explain-command...
Re: Question about explain-command...
List pgsql-performance
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

pgsql-performance by date:

Previous
From: "Hakan Kocaman"
Date:
Subject: Re: in memory views
Next
From: Tino Wildenhain
Date:
Subject: Re: in memory views