Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id A21C1632702 for ; Sun, 28 Mar 2010 03:52:04 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 08691-02 for ; Sun, 28 Mar 2010 06:51:49 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-vw0-f46.google.com (mail-vw0-f46.google.com [209.85.212.46]) by mail.postgresql.org (Postfix) with ESMTP id 5919563246F for ; Sun, 28 Mar 2010 03:51:05 -0300 (ADT) Received: by vws8 with SMTP id 8so2055163vws.19 for ; Sat, 27 Mar 2010 23:51:04 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:date:received:message-id :subject:from:to:content-type; bh=i38MiK6N+SdMYdI72KSr5b5Cd82bG0a3eijrOgV6cP4=; b=qJKGTKdBXqODaLqlsjSk7e/Tf2XutpZjAFJtwVcqAnYsex9mhgH8Q/cR0MzYAg4sLh GdctRu7L4B3taOcWCo0Ee558354wYDIa495FjxtIzjT8z4hahzeY0yfUmHdg1p3oHAyt qJJH/klSKAgnexDg46KPr0AlSoL9VjO62nCGU= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:date:message-id:subject:from:to:content-type; b=lfdfY0JzbuhcmWERt73I+dvwN+wj/qhXSacr5evJBCktN4sznyI2/D0soW+8FY9n95 /efsX8thIJ/feGgd2D79mZUM1VBC17DSGKvPObnuLfIJ77B03JTxz9y8yGiswnnVAAPW MSVUk8hCmuTcRlnvRf3VEI5zwjQOrbkH2+F7s= MIME-Version: 1.0 Received: by 10.220.99.197 with HTTP; Sat, 27 Mar 2010 23:51:04 -0700 (PDT) Date: Sun, 28 Mar 2010 12:21:04 +0530 Received: by 10.220.124.129 with SMTP id u1mr2030535vcr.25.1269759064274; Sat, 27 Mar 2010 23:51:04 -0700 (PDT) Message-ID: <645d9d71003272351x766707c7sadde9aa77bc1c0d3@mail.gmail.com> Subject: Optimizer showing wrong rows in plan From: Tadipathri Raghu To: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=00163691fbc5f9f8560482d6d142 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.971 tagged_above=-10 required=5 tests=AWL=0.627, BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201003/348 X-Sequence-Number: 38173 --00163691fbc5f9f8560482d6d142 Content-Type: text/plain; charset=ISO-8859-1 Hi All, Example on optimizer =============== postgres=# create table test(id int); CREATE TABLE postgres=# insert into test VALUES (1); INSERT 0 1 postgres=# select * from test; id ---- 1 (1 row) postgres=# explain select * from test; QUERY PLAN -------------------------------------------------------- Seq Scan on test (cost=0.00..34.00 *rows=2400* width=4) (1 row) In the above, example the optimizer is retreiving those many rows where there is only one row in that table. If i analyze am geting one row. postgres=# ANALYZE test; ANALYZE postgres=# explain select * from test; QUERY PLAN ---------------------------------------------------- Seq Scan on test (cost=0.00..1.01 *rows=1* width=4) (1 row) My question here is, what it retreiving as rows when there is no such. One more thing, if i wont do analyze and run the explain plan for three or more times, then catalogs getting updated automatically and resulting the correct row as 1. Q2. Does explain , will update the catalogs automatically. Regards Raghavendra --00163691fbc5f9f8560482d6d142 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
Hi All,
=A0
Example on optimizer
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
postgres=3D# create table test(id int);
CREATE TABLE
postgres=3D= # insert into test VALUES (1);
INSERT 0 1
postgres=3D# select * from = test;
=A0id
----
=A0 1
(1 row)
postgres=3D# explain select * from test;
=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 QUERY PLAN
------------------= --------------------------------------
=A0Seq Scan on test=A0 (cost=3D0.= 00..34.00 rows=3D2400 width=3D4)
(1 row)
In the above, example the optimizer is retreiving those many rows wher= e there is only one row in that table. If i analyze am geting one row.
=A0
postgres=3D# ANALYZE test;
ANALYZE
postgres=3D# explain select *= from test;
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= QUERY PLAN
----------------------------------------------------
=A0S= eq Scan on test=A0 (cost=3D0.00..1.01 rows=3D1 width=3D4)<= br> (1 row)
=A0
My question here is, what it retreiving as rows when there is no such.= One more thing, if i wont do analyze and run the explain plan for three or= more times, then catalogs getting updated automatically and resulting the = correct row as 1.
=A0
Q2. Does explain , will update the catalogs automatically.
=A0
Regards
Raghavendra
=A0
--00163691fbc5f9f8560482d6d142--