From tszczachor@zke.com.pl Mon Apr 3 05:49:52 2000 Received: from news.tht.net (news.hub.org [216.126.91.242]) by hub.org (8.9.3/8.9.3) with ESMTP id BAA35658 for ; Mon, 3 Apr 2000 01:49:52 -0400 (EDT) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.9.3/8.9.3) id BAA43453 for pgsql-sql@postgresql.org; Mon, 3 Apr 2000 01:48:56 -0400 (EDT) (envelope-from news) X-Authentication-Warning: news.tht.net: news set sender to using -f From: tszczachor@zke.com.pl (=?iso-8859-2?Q?Tomasz_Szcz=B1chor?=) X-Newsgroups: comp.databases.postgresql.sql Subject: approve VKPts5 unsubscribe pgsql Date: 3 Apr 2000 01:48:55 -0400 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 26 Message-ID: <006c01bf9d2b$bc6bd000$020018ac@zke.com.pl> X-Trace: news.tht.net 954740936 43450 216.126.91.242 (3 Apr 2000 05:48:56 GMT) X-Complaints-To: scrappy@hub.org Organisation: Hub.Org Networking Services (http://www.hub.org) To: pgsql-sql@postgresql.org X-Archive-Number: 200004/15 This is a multi-part message in MIME format. ------=_NextPart_000_0069_01BF9D3C.7C5AFE80 Content-Type: text/plain; charset="iso-8859-2" Content-Transfer-Encoding: quoted-printable ------=_NextPart_000_0069_01BF9D3C.7C5AFE80 Content-Type: text/html; charset="iso-8859-2" Content-Transfer-Encoding: quoted-printable
 
------=_NextPart_000_0069_01BF9D3C.7C5AFE80-- From avm@webclub.ru Mon Apr 3 11:46:15 2000 Received: from holmes.infopro.spb.su (holmes.infopro.spb.su [195.242.2.2]) by hub.org (8.9.3/8.9.3) with ESMTP id HAA00285 for ; Mon, 3 Apr 2000 07:46:15 -0400 (EDT) (envelope-from avm@webclub.ru) Received: from barrymore.peterlink.ru (barrymore.peterlink.ru [195.242.2.8]) by holmes.infopro.spb.su (8.9.1/8.9.1) with ESMTP id PAA18534 for ; Mon, 3 Apr 2000 15:46:07 +0400 (MSD) Received: from turtle.interforge.ru (interforge.dialup.peterlink.ru [195.242.31.96]) by barrymore.peterlink.ru (8.9.1/8.9.1) with ESMTP id PAA07206 for ; Mon, 3 Apr 2000 15:46:05 +0400 (MSD) Date: Mon, 3 Apr 2000 15:48:05 +0400 From: "Alexey V. Meledin" X-Mailer: The Bat! (v1.36) S/N F29DEE5D / Educational Reply-To: "Alexey V. Meledin" Organization: InterForge Developers Group, Spb X-Priority: 3 (Normal) Message-ID: <3658.000403@webclub.ru> To: pgsql-sql@postgresql.org Subject: Re: approve VKPts5 unsubscribe pgsql In-reply-To: <006c01bf9d2b$bc6bd000$020018ac@zke.com.pl> References: <006c01bf9d2b$bc6bd000$020018ac@zke.com.pl> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200004/17 Hi! 1. I have: vladimir=> explain SELECT acc.ifs_account_id FROM vladimir-> ifs_account acc, vladimir-> ifs_tree_default def, vladimir-> ifs_account_tree_data atd vladimir-> WHERE vladimir-> acc.ifs_status_id = 12 vladimir-> AND atd.ifs_tree_id IN(14,26) vladimir-> AND def.ifs_tree_default_id IN(587,175) vladimir-> AND atd.ifs_account_id = acc.ifs_account_id vladimir-> AND atd.ifs_data_id = def.ifs_data_id; NOTICE: QUERY PLAN: Nested Loop (cost=0.00 rows=1 width=16) -> Seq Scan on ifs_account acc (cost=0.00 rows=0 width=4) -> Seq Scan (cost=757.02 rows=1 width=12) (**) -> ??? (cost=757.02 rows=1 width=12) -> Hash Join (cost=757.02 rows=1 width=12) -> Seq Scan on ifs_account_tree_data atd (cost=751.76 rows=3 width=8 ) -> Hash (cost=4.07 rows=3 width=4) -> Index Scan using xpkifs_tree_default, xpkifs_tree_default on ifs_tree_default def (cost=4.07 rows=3 width=4) What mean "???" at (**). There is no index? Or unknown method? 2. Seq Scan - Sequential Scan - this mean FULL TABLE SCAN (DATA SCAN)? 3. I try to play with indeces on this tables 3.1. Setting all btree indeces explain SELECT acc.ifs_account_id FROM ifs_account acc, ifs_tree_default def, ifs_account_tree_data atd WHERE acc.ifs_status_id = 12 AND atd.ifs_tree_id IN(14,26) AND def.ifs_tree_default_id IN(587,175) AND atd.ifs_account_id = acc.ifs_account_id AND atd.ifs_data_id = def.ifs_data_id; Nested Loop (cost=0.00 rows=1 width=16) -> Nested Loop (cost=0.00 rows=1 width=12) -> Seq Scan on ifs_tree_default def (cost=0.00 rows=0 width=4) -> Seq Scan on ifs_account_tree_data atd (cost=0.00 rows=0 width=8) -> Seq Scan on ifs_account acc (cost=0.00 rows=0 width=4) Not optimal way I think 2. I've replace btree indexes on relation AND atd.ifs_data_id = def.ifs_data_id; with: create index itd_hash_data_id on ifs_tree_default USING HASH (ifs_data_id); create index iatd_hash_data_id on ifs_account_tree_data USING HASH (ifs_data_id); PS: Can't replace on on ifs_account_id because of PRIMARY KEY (may be direct Alternative Entry helps me, but is it right way to optimize?) so: Nested Loop (cost=0.00 rows=1 width=16) -> Seq Scan on ifs_account acc (cost=0.00 rows=0 width=4) -> Seq Scan (cost=10.17 rows=1 width=12) -> ??? (cost=10.17 rows=1 width=12) -> Nested Loop (cost=10.17 rows=1 width=12) -> Index Scan using xpkifs_tree_default, xpkifs_tree_default on ifs_tree_default def (cost=4.07 ows=3 width=4) -> Index Scan using xif588ifs_account_tree_data on ifs_account_tree_d ata atd (cost=2.03 rows=3 width=8) 3. I've delete all indexes on AND atd.ifs_data_id = def.ifs_data_id; Then I've got: Nested Loop (cost=0.00 rows=1 width=16) -> Seq Scan on ifs_account acc (cost=0.00 rows=0 width=4) -> Seq Scan (cost=757.02 rows=1 width=12) -> ??? (cost=757.02 rows=1 width=12) -> Hash Join (cost=757.02 rows=1 width=12) -> Seq Scan on ifs_account_tree_data atd (cost=751.76 rows=3 width=8 ) -> Hash (cost=4.07 rows=3 width=4) -> Index Scan using xpkifs_tree_default, xpkifs_tree_default on ifs_tree_default def (cost=4.07 rows=3 width=4) Hash Join appears. But there is no indexes, so "Table Scan" appears (approximately 18000 rows in ifs_account_tree_data)? BAD!?! So, where is the optimal way? To make separate queries and use temporary tables to decrease JOINs? Regards, Alexey V. Meledin InterForge Developers Group, Saint-Petersburg look_to: >>>>>>>>>>>>>>>> mail_to: >>>