Thread: Question about explain-command...
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
> -----Original Message----- > From: pgsql-performance-owner@postgresql.org > [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of > Clemens Eisserer > Sent: Wednesday, May 10, 2006 6:50 AM > To: pgsql-performance@postgresql.org > Subject: [PERFORM] Question about explain-command... > > 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... > For small tables, it is faster to do a sequential scan than an index scan. You probably don't have enough test data to make the planner choose an index scan. I don't think the quotes really mean anything. They are just used as delimiters. The hash lines mean your tables are being joined by hash joins. You should read this page for more info: http://www.postgresql.org/docs/8.1/interactive/performance-tips.html
I will try answering your questions. Please note that I am a newbie myself.
Clemens Eisserer wrote
> 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?
Yes. Probably each table fits nicely into a single disk read, so reading
both the index AND the table is going to be twice as expensive.
> Why are some table-names and some column-names surrounded by ' " '?
> Are they threated as text-columns?
They are either names generated by postgres ("outer" and "inner") or
field names which are also reserved words in SQL ("key"). You can always
use double quotes around a field name - you have to in some cases if
they are reserved words, and always if they contain "special characters"
(not sure from memory exactly which these are - at least spaces). I
recommend not to use either of these, even if a reserved word is the
best description of your field.
Postgres seems to be a bit better than some other dbms's in allowing
unquoted reserved words as field names if there is no ambiguity. Thsis
may mean that you get a problem if your application is ever ported to a
different dbms.
> 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...
Try loading your tables with a realistic number of customers, and you
should see a change in the query plan to use your precious indexes.
/Nis
On Wed, May 10, 2006 at 09:47:07AM -0500, Dave Dutcher wrote: > The hash lines mean your tables are being joined by hash joins. You > should read this page for more info: > > http://www.postgresql.org/docs/8.1/interactive/performance-tips.html <tooting-own-horn>You might also want to read http://www.pervasivepostgres.com/instantkb13/article.aspx?id=10120&query=explain -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461