X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 492EDD1BC5C for ; Tue, 2 Dec 2003 19:01:15 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 77317-06 for ; Tue, 2 Dec 2003 15:00:49 -0400 (AST) Received: from smtp.istop.com (dci.doncaster.on.ca [66.11.168.194]) by svr1.postgresql.org (Postfix) with ESMTP id 27689D1B445 for ; Tue, 2 Dec 2003 15:00:44 -0400 (AST) Received: from stark.dyndns.tv (gsstark.mtl.istop.com [66.11.160.162]) by smtp.istop.com (Postfix) with ESMTP id 172AD36E82; Tue, 2 Dec 2003 13:59:57 -0500 (EST) Received: from localhost ([127.0.0.1] helo=stark.dyndns.tv ident=foobar) by stark.dyndns.tv with smtp (Exim 3.36 #1 (Debian)) id 1ARFka-0004fq-00; Tue, 02 Dec 2003 13:59:56 -0500 To: Jared Carr Cc: Robert Treat , pgsql-performance@postgresql.org Subject: Re: A question on the query planner References: <3FCBB639.10409@89glass.com> <1070385381.24915.8650.camel@camel> <3FCCD225.8030609@89glass.com> In-Reply-To: <3FCCD225.8030609@89glass.com> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 02 Dec 2003 13:59:56 -0500 Message-ID: <87d6b79har.fsf@stark.dyndns.tv> Lines: 34 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/28 X-Sequence-Number: 4888 Jared Carr writes: > Furthermore noticed that in the following query plan it is doing the > sequential scan on quotes first, and then doing the sequential on zips. IMHO > this should be the other way around, since the result set for zips is > considerably smaller especially give that we are using a where clause to > limit the number of items returned from zips, so it would seem that it would > be faster to scan zips then join onto quotes, but perhaps it needs to do the > sequential scan on both regardless. >-> Hash Join (cost=1088.19..56382.58 rows=4058 width=62) (actual time=86.111..1834.682 rows=10193 loops=1) > Hash Cond: (("outer".zip)::text = ("inner".zip)::text) > -> Seq Scan on quotes q (cost=0.00..10664.25 rows=336525 width=27) (actual time=0.098..658.905 rows=336963 loops=1) > -> Hash (cost=1086.90..1086.90 rows=516 width=52) (actual time=85.798..85.798 rows=0 loops=1) > -> Seq Scan on zips z (cost=0.00..1086.90 rows=516 width=52) (actual time=79.532..84.151 rows=718 loops=1) > Filter: ((state)::text = 'WA'::text) You're misreading it. Hash join is done by reading in one table into a hash table, then reading the other table looking up entries in the hash table. The zips are being read into the hash table which is appropriate if it's the smaller table. > Of course still there is the holy grail of getting it to actually use > the indexes. :P > Merge Cond: ("outer"."?column7?" = "inner"."?column5?") Well it looks like you have something strange going on. What data type is car_id in each table? -- greg