X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 81E8856587 for ; Sun, 13 Mar 2005 19:16:05 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 08129-09 for ; Sun, 13 Mar 2005 19:15:56 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id B091652A56 for ; Sun, 13 Mar 2005 19:15:55 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j2DJFsvr018382; Sun, 13 Mar 2005 14:15:54 -0500 (EST) To: John Arbash Meinel Cc: =?ISO-8859-2?Q?Miroslav_=A9ulc?= , PGSQL mailing list Subject: Re: How to read query plan In-reply-to: <423478B1.7080106@arbash-meinel.com> References: <42345D24.8070104@startnet.cz> <4234692E.3050802@arbash-meinel.com> <42347407.2000202@startnet.cz> <423478B1.7080106@arbash-meinel.com> Comments: In-reply-to John Arbash Meinel message dated "Sun, 13 Mar 2005 11:30:25 -0600" Date: Sun, 13 Mar 2005 14:15:54 -0500 Message-ID: <18381.1110741354@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/226 X-Sequence-Number: 10931 John Arbash Meinel writes: > How about a quick side track. > Have you played around with your shared_buffers, maintenance_work_mem, > and work_mem settings? Indeed. The hash joins seem unreasonably slow considering how little data they are processing (unless this is being run on some ancient toaster...). One thought that comes to mind is that work_mem may be set so small that the hashes are forced into multiple batches. Another question worth asking is what are the data types of the columns being joined on. If they are character types, what locale and encoding is the database using? > Are you re-running the query multiple times, and reporting the later > speeds, or just the first time? (If nothing is loaded into memory, the > first run is easily 10x slower than later ones.) That cost would be paid during the bottom-level scans though. The thing that strikes me here is that nearly all of the cost is being spent joining. > What version of postgres are you using? And what's the platform (hardware and OS)? regards, tom lane