Received: from mail.interchange.ca (ns.interchange.ca [216.126.79.2]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f4N5FsA35622 for ; Wed, 23 May 2001 01:15:54 -0400 (EDT) (envelope-from michael@fastmail.ca) Received: by mail.interchange.ca (Fastmailer, from userid 555) id 8E6652126; Wed, 23 May 2001 01:15:34 -0400 (EDT) MIME-Version: 1.0 Message-Id: <3B0B4776.0001A9.50190@frodo.searchcanada.ca> To: pgsql-sql@postgresql.org Subject: index/join madness From: "Michael Richards" X-Fastmail-IP: 24.43.130.237 Date: Wed, 23 May 2001 01:15:34 -0400 (EDT) Content-Transfer-Encoding: 7bit Content-Type: Text/Plain X-Archive-Number: 200105/246 X-Sequence-Number: 3074 Ok, I've built the most insane query ever. It joins 11 tables, most of which are the same table, just extracting different values. Here is the query plan: Nested Loop (cost=0.00..5011.89 rows=1 width=156) -> Nested Loop (cost=0.00..4191.82 rows=1 width=140) -> Nested Loop (cost=0.00..4189.79 rows=1 width=112) -> Nested Loop (cost=0.00..4188.58 rows=1 width=104) -> Nested Loop (cost=0.00..4186.55 rows=1 width=88) -> Nested Loop (cost=0.00..3366.48 rows=1 width=72) -> Nested Loop (cost=0.00..2546.41 rows=1 width=68) -> Nested Loop (cost=0.00..1726.34 rows=1 width=52) -> Nested Loop (cost=0.00..906.27 rows=1 width=32) -> Seq Scan on formdata f6 (cost=0.00..904.16 rows=1 width=4) -> Index Scan using users_pkey on users u (cost=0.00..2.02 rows=1 width=28) SubPlan -> Seq Scan on sentletters (cost=0.00..0.00 rows=1 width=4) -> Seq Scan on formdata f5 (cost=0.00..818.42 rows=131 width=20) -> Seq Scan on formdata f2 (cost=0.00..818.42 rows=131 width=16) -> Seq Scan on formdata f1 (cost=0.00..818.42 rows=131 width=4) -> Seq Scan on formdata f3 (cost=0.00..818.42 rows=131 width=16) -> Index Scan using formmvl_pkey on formmvl m (cost=0.00..2.02 rows=1 width=16) -> Seq Scan on relations r (cost=0.00..1.12 rows=7 width=8) -> Index Scan using users_pkey on users u2 (cost=0.00..2.02 rows=1 width=28) -> Seq Scan on formdata f4 (cost=0.00..818.42 rows=131 width=16) If anyone has a screen wide enough to see this, you will see that the majority of the time is spent doing sequential scans on the formdata table. This table needs formid, fieldid and userid to find the value I'm looking for. It has one index defined on: Index "formdata_pkey" Attribute | Type -----------+--------- formid | integer occid | integer userid | integer fieldid | integer unique btree (primary key) In my case I'm ignoring occid since it's always 1 for these values. Is there any way I can coerce this into using a multifield index? My joins generally look like: JOIN formdata AS f2 ON (u.id=f2.userid AND f2.formid=1 AND f2.fieldid=2) I'm a little curious as to why it's not using the primary key... Finally, I'm planning on moving this to 7.2 and converting all the joins to use outer joins. Will there be a significant penalty in performance running outer joins? -Michael _________________________________________________________________ http://fastmail.ca/ - Fast Free Web Email for Canadians From pgsql-sql-owner@postgresql.org Wed May 23 05:21:24 2001 Received: from gate.no9.cz ([194.228.119.34]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f4N963A57578 for ; Wed, 23 May 2001 05:06:03 -0400 (EDT) (envelope-from minca_sql@no9.cz) Received: from mincak ([10.1.1.11]) by gate.no9.cz (8.9.3/8.9.3) with SMTP id JAA07522 for ; Wed, 23 May 2001 09:47:43 +0200 Message-ID: <009301c0e368$589c0de0$0b01010a@no9.cz> From: "Martin Smetak" To: Subject: Recursive select Date: Wed, 23 May 2001 11:11:26 +0200 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-2" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2615.200 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2615.200 X-Archive-Number: 200105/247 X-Sequence-Number: 3075 Hi all! Anyone know if it's possible to make a recursive select from a table ? My problem: I got a table of "some categories" which all points to its parrent one(tree)...shown below. And I want to select all names of parrent categories of one child, lets say "fast[4]". Now I'm solving that with many SQL queries like : "SELECT main_id FROM cat WHERE id=4;"....but I would like to optimize this. Anyone can help or point me to a way ?? Thanks a lot,Minca Table : CAT ID | Main_id | Name ========================= 1 0 Car 2 1 Crash 3 1 Wash 4 3 Fast 5 1 Second_hand 6 0 House 7 3 Slow ....etc *(root)[0] -Car[1] -Crash[2] -Wash[3] -Fast[4] -Slow[7] -Second hand[5] -House[6]