Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 38282633F80 for ; Thu, 1 Apr 2010 13:52:35 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 60589-04 for ; Thu, 1 Apr 2010 16:52:24 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from qw-out-2122.google.com (qw-out-2122.google.com [74.125.92.26]) by mail.postgresql.org (Postfix) with ESMTP id 977BF63711A for ; Thu, 1 Apr 2010 13:31:03 -0300 (ADT) Received: by qw-out-2122.google.com with SMTP id 8so442911qwh.61 for ; Thu, 01 Apr 2010 09:31:02 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:sender:received:in-reply-to :references:date:x-google-sender-auth:received:message-id:subject :from:to:cc:content-type; bh=2bOILlzzPRYZzDUjueh8bIQbp26JZNeWGB6igrnYUfQ=; b=M5vopyk8d0nJ2tWcHGRinZJKZBI0vTOCMk/KqK/oLNJYpHQSXTGRZv4cStjcI4iN/L R+8hWd1GGyktcq5ar8tMQGQFqTK1ZX/ovW5x4UgS74Pkz7/trqUOezKAPT6hFZwSZPlu jDMYKYK3w5dL6Vz6pxbRlkS2ENhhm6Xjerp00= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:sender:in-reply-to:references:date :x-google-sender-auth:message-id:subject:from:to:cc:content-type; b=DwQrfEMV7XSSrjei3d29oKCydipHpK+9k3LTquLD5ABu5LGPOLX1bdEAmDjKPkm1am fRpfbOkS1hkkpmF1uGrxuxMe1g8HqIMKqo+QC/4wvuyH4aa6UQoZMbQ0Geer1RJ0FJDV 150Pn9jDyAEJ/j9NWv22i7+7MBVi1iQ0fIN9A= MIME-Version: 1.0 Received: by 10.229.49.143 with HTTP; Thu, 1 Apr 2010 09:31:02 -0700 (PDT) In-Reply-To: References: <603c8f071003291102g778afc2fvda0fc281454526c9@mail.gmail.com> <4BB1DB9A020000250003020E@gw.wicourts.gov> <603c8f071003301259u140d8556vc54ff46855013bd3@mail.gmail.com> Date: Thu, 1 Apr 2010 12:31:02 -0400 X-Google-Sender-Auth: e48bd69c366bc215 Received: by 10.229.191.18 with SMTP id dk18mr1590297qcb.9.1270139462628; Thu, 01 Apr 2010 09:31:02 -0700 (PDT) Message-ID: Subject: Re: experiments in query optimization From: Eliot Gable To: Faheem Mitha Cc: Robert Haas , Kevin Grittner , pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=0016363b90927c371904832f6395 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.002 tagged_above=-10 required=5 tests=BAYES_50=0.001, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201004/9 X-Sequence-Number: 38259 --0016363b90927c371904832f6395 Content-Type: text/plain; charset=ISO-8859-1 On Thu, Apr 1, 2010 at 7:46 AM, Faheem Mitha wrote: > Looking at this more closely, idlink_id and anno_id are primary keys, so > already have indexes on them, so my understanding (from the docs) is there > is no purpose in creating them. That's why I removed the indexes that were > there (back last August, actually, according to my logs). Anyway, doesn't > look there is anything I can do here. Does anyone have additions or > corrections to this? > > When you do a join, you typically have a foreign key in one table referencing a primary key in another table. While designating a foreign key does put a constraint on the key to ensure referential integrity, it does not put an index on the column that is being designated as a foreign key. If I understand correctly, the scan done as the inner loop of the nested loop scan for the join is going to be your foreign key column, not your primary key column. Thus, if you have no index on the foreign key column, you will be forced to do a sequential table scan to do the join. In that case the hash-based join will almost certainly be faster (especially for such a large number of rows). If you put an index on the foreign key, then the inner scan can be an index scan and that might turn out to be faster than building the hash indexes on all the table rows. Somebody can correct me if I'm wrong. -- Eliot Gable "We do not inherit the Earth from our ancestors: we borrow it from our children." ~David Brower "I decided the words were too conservative for me. We're not borrowing from our children, we're stealing from them--and it's not even considered to be a crime." ~David Brower "Esse oportet ut vivas, non vivere ut edas." (Thou shouldst eat to live; not live to eat.) ~Marcus Tullius Cicero --0016363b90927c371904832f6395 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable

On Thu, Apr 1, 2010 at 7:46 AM, Faheem M= itha <faheem@e= mail.unc.edu> wrote:
=A0
Looking at this more closely, idlink_id and anno_id are primary keys, so al= ready have indexes on them, so my understanding (from the docs) is there is= no purpose in creating them. That's why I removed the indexes that wer= e there (back last August, actually, according to my logs). Anyway, doesn&#= 39;t look there is anything I can do here. Does anyone have additions or co= rrections to this?


When you do a join, you typicall= y have a foreign key in one table referencing a primary key in another tabl= e. While designating a foreign key does put a constraint on the key to ensu= re referential integrity, it does not put an index on the column that is be= ing designated as a foreign key. If I understand correctly, the scan done a= s the inner loop of the nested loop scan for the join is going to be your f= oreign key column, not your primary key column. Thus, if you have no index = on the foreign key column, you will be forced to do a sequential table scan= to do the join. In that case the hash-based join will almost certainly be = faster (especially for such a large number of rows). If you put an index on= the foreign key, then the inner scan can be an index scan and that might t= urn out to be faster than building the hash indexes on all the table rows. =

Somebody can correct me if I'm wrong.


--
Eliot Gable

"We do not inherit the Earth from ou= r ancestors: we borrow it from our children." ~David Brower

"I decided the words were too conservative for me. We're not borro= wing from our children, we're stealing from them--and it's not even= considered to be a crime." ~David Brower

"Esse oportet ut= vivas, non vivere ut edas." (Thou shouldst eat to live; not live to e= at.) ~Marcus Tullius Cicero
--0016363b90927c371904832f6395--