pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuestion about explain-command...
4+ messages / 4 participants
[nested] [flat]
* Question about explain-command...
@ 2006-05-10 11:49 Clemens Eisserer <linuxhippy@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Clemens Eisserer @ 2006-05-10 11:49 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Question about explain-command...
@ 2006-05-10 14:47 Dave Dutcher <dave@tridecap.com>
parent: Clemens Eisserer <linuxhippy@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Dave Dutcher @ 2006-05-10 14:47 UTC (permalink / raw)
To: 'Clemens Eisserer' <linuxhippy@gmail.com>; pgsql-performance
> -----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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Question about explain-command...
@ 2006-05-10 15:02 Nis Jorgensen <nis@superlativ.dk>
parent: Clemens Eisserer <linuxhippy@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Nis Jorgensen @ 2006-05-10 15:02 UTC (permalink / raw)
To: Clemens Eisserer <linuxhippy@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Question about explain-command...
@ 2006-05-11 22:41 Jim C. Nasby <jnasby@pervasive.com>
parent: Dave Dutcher <dave@tridecap.com>
0 siblings, 0 replies; 4+ messages in thread
From: Jim C. Nasby @ 2006-05-11 22:41 UTC (permalink / raw)
To: Dave Dutcher <dave@tridecap.com>; +Cc: Clemens Eisserer <linuxhippy@gmail.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-05-11 22:41 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-05-10 11:49 Question about explain-command... Clemens Eisserer <linuxhippy@gmail.com>
2006-05-10 14:47 ` Dave Dutcher <dave@tridecap.com>
2006-05-11 22:41 ` Jim C. Nasby <jnasby@pervasive.com>
2006-05-10 15:02 ` Nis Jorgensen <nis@superlativ.dk>
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