Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 361051337B47 for ; Wed, 11 May 2011 16:07:16 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 55055-07 for ; Wed, 11 May 2011 19:07:08 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-pz0-f46.google.com (mail-pz0-f46.google.com [209.85.210.46]) by mail.postgresql.org (Postfix) with ESMTP id 71DFB133798E for ; Wed, 11 May 2011 16:07:08 -0300 (ADT) Received: by pzk9 with SMTP id 9so375337pzk.19 for ; Wed, 11 May 2011 12:07:07 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:in-reply-to:references:date :message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=4aD4f53SzC4AoXZSXpXXEhA2PsGmrNxLVl0AoWFSSbY=; b=Z8/pgeJZgbjPikey9SiDrnNhVguEdojGwDS7EsFvNKVwEp2LiwwlNsXGGv4o0mFIzX N4bZ6N3tpQTyAcuIhLdzj/YGfUs+1nlximaEbHMOYBgxSoJ05kEh7qPWenVrDZA6Wxap ab7LH//UbJ3HSYiC2mkSeDd7sqvQOfc58Xid8= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; b=uC14qpFF3y2wmt7zkHyRfK21I5DmAlVXxGZTYhw4A8t7454l/UREUbbXWhbc1xu1VL 3Q2hpBGbhS6yz3ZvNR9FI6hZTdsqBOJJgFh1JApktFRkqaCit7Tw0c3ZMAdQAyeXIeP+ 2nmVfb+TuBh1g+/HdEJsOcJ+oM5vZvGeyrK1M= MIME-Version: 1.0 Received: by 10.68.27.170 with SMTP id u10mr14199237pbg.529.1305140827773; Wed, 11 May 2011 12:07:07 -0700 (PDT) Received: by 10.68.49.7 with HTTP; Wed, 11 May 2011 12:07:07 -0700 (PDT) In-Reply-To: References: Date: Wed, 11 May 2011 12:07:07 -0700 Message-ID: Subject: Re: 'Interesting' prepared statement slowdown on large table join From: Jeff Janes To: "Prodan, Andrei" Cc: pgsql-performance@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.898 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, RFC_ABUSE_POST=0.001 X-Spam-Level: X-Archive-Number: 201105/157 X-Sequence-Number: 43666 On Wed, May 11, 2011 at 4:08 AM, Prodan, Andrei wrote: > ... > > > The select is as follows: > prepare ps(varchar,varchar,varchar) as select party.party_id from party, > big_table where external_id =3D $1 and party.party_id =3D big_table.party= _id > and attr_name =3D $2 and attr_value =3D $3; > PREPARE > execute ps('13','GroupId','testshop'); > > BAD PLAN: > testdb=3D# explain analyze execute ps('13','GroupId','testshop'); > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0QUERY ... > =A0 -> =A0Index Scan using attr_name_value on big_table =A0(cost=3D0.00..= 22.85 > rows=3D4 width=3D7) (actual time=3D0.176..757.646 rows=3D914786 loops=3D1= ) > =A0 =A0 =A0 =A0 Index Cond: (((attr_name)::text =3D ($2)::text) AND > ((attr_value)::text =3D ($3)::text)) So it expects 4 rows and finds 914786, essentially the whole table. So that is bad. But what is it thinking during the GOOD PLAN state? A possible way to get that information is to prepare a simpler prepared statement that omits the join to party and explain analyze it with the same params for attr_name and attr_value. If that gives you the full table scan rather than index scan, then you can "set enable_seqscan=3Doff" try to force the index scan. Cheers, Jeff