X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id EF99D9FB284 for ; Tue, 19 Sep 2006 06:21:31 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 70486-02 for ; Tue, 19 Sep 2006 09:21:27 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from bay0-omc2-s32.bay0.hotmail.com (bay0-omc2-s32.bay0.hotmail.com [65.54.246.168]) by postgresql.org (Postfix) with ESMTP id 62F8F9FB27C for ; Tue, 19 Sep 2006 06:21:27 -0300 (ADT) Received: from hotmail.com ([64.4.11.18]) by bay0-omc2-s32.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.1830); Tue, 19 Sep 2006 02:21:25 -0700 Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Tue, 19 Sep 2006 02:21:25 -0700 Message-ID: Received: from 80.24.52.85 by by7fd.bay7.hotmail.msn.com with HTTP; Tue, 19 Sep 2006 09:21:22 GMT X-Originating-IP: [80.24.52.85] X-Originating-Email: [im-james@hotmail.com] X-Sender: im-james@hotmail.com From: "James Im" To: pgsql-sql@postgresql.org Subject: inner join is much faster! is that right? Date: Tue, 19 Sep 2006 09:21:22 +0000 Mime-Version: 1.0 Content-Type: text/plain; format=flowed X-OriginalArrivalTime: 19 Sep 2006 09:21:25.0796 (UTC) FILETIME=[FB068E40:01C6DBCC] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=2.421 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, MSGID_FROM_MTA_HEADER, PLING_QUERY, SPF_PASS X-Spam-Level: ** X-Archive-Number: 200609/120 X-Sequence-Number: 26331 I really thought that Postgresql would rewrite a query from select * from file a, file_tag b, tag c where a.id_file=b.id_file and b.id_tag=c.id_tag and a.id_file=10000001000000 to something like: select * from (file a inner join file_tag b on (a.id_file=b.id_file)) inner join tag c on (b.id_tag=c.id_tag) where a.id_file=10000001000000 However this is not the case! I have a many-to-many relation between 2 tables (tag and file). tag has 40000 rows and file has 5 millions rows. file_tag has 50 millions rows. In this setup, the first select takes a couple of minutes to complete while the second query takes only a second to complete. Is this a normal expected behavior? Shouldn't Postgresql be able to rewrite the query correctly? _________________________________________________________________ Få 250 MB gratis lagerplads på MSN Hotmail: http://www.hotmail.com