Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id F2B091F02EF3 for ; Thu, 26 Apr 2012 16:17:32 -0300 (ADT) Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SNUC3-0002Cm-CF for pgsql-performance@postgresql.org; Thu, 26 Apr 2012 19:17:32 +0000 Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id q3QJHIQY012229; Thu, 26 Apr 2012 15:17:18 -0400 (EDT) To: Richard Kojedzinszky cc: pgsql-performance@postgresql.org Subject: Re: query optimization In-reply-to: References: Comments: In-reply-to Richard Kojedzinszky message dated "Tue, 24 Apr 2012 10:11:43 +0200" Date: Thu, 26 Apr 2012 15:17:18 -0400 Message-ID: <12228.1335467838@sss.pgh.pa.us> From: Tom Lane X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201204/163 X-Sequence-Number: 46809 Richard Kojedzinszky writes: > Dear list, > We have a database schema, which looks the same as the attached script. > When filling the tables with data, and skipping analyze on the table (so > pg_stats contains no records for table 'a'), the first select in the > script runs fast, but after an analyze the planner decides to sequence > scan tables b and c, thus making the query much slower. Can somebody help > me solving this issue, or tuning our installation to not to use sequence > scans in this case? Um ... did you analyze all the tables, or just some of them? I get sub-millisecond runtimes if all four tables have been analyzed, but it does seem to pick lousy plans if, say, only a and b have been analyzed. What you really need for this query structure is the parameterized-path work I've been doing for 9.2; but at least on the exact example given, I'm not seeing that 9.1 is that much worse. regards, tom lane