Re: [PERFORM] Question about explain-command...
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-hornYou might also want to read http://www.pervasivepostgres.com/instantkb13/article.aspx?id=10120query=explain -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] 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=111) 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 rows=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 width=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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Question about explain-command...
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Question about explain-command...
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=111) 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 rows=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 width=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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
