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 04F768B9EF1 for ; Sun, 13 Feb 2005 21:27:48 +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 30018-06 for ; Sun, 13 Feb 2005 21:27:46 +0000 (GMT) Received: from rproxy.gmail.com (rproxy.gmail.com [64.233.170.201]) by svr1.postgresql.org (Postfix) with ESMTP id DF9FD8B9EB1 for ; Sun, 13 Feb 2005 21:27:45 +0000 (GMT) Received: by rproxy.gmail.com with SMTP id j1so541621rnf for ; Sun, 13 Feb 2005 13:27:45 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type:content-transfer-encoding; b=OvZtyfHtOqe2cIKSSByvb1UADzPDrlOo88V+AWGgFBggwfhmhgvyPycAFTi9SjEf5xOfpadRLLIEd7fz8zvIeNLpA1gqtG8O8RfLMVHSYPspCSpc4wDzKsvNnfcwMGyoyyzwudPMZR98qpukYH1mMiX1zLSKKV713hj+668rnj8= Received: by 10.39.3.49 with SMTP id f49mr231370rni; Sun, 13 Feb 2005 13:27:45 -0800 (PST) Received: by 10.38.97.31 with HTTP; Sun, 13 Feb 2005 13:27:45 -0800 (PST) Message-ID: Date: Sun, 13 Feb 2005 16:27:45 -0500 From: Jaime Casanova Reply-To: Jaime Casanova To: performance pgsql Subject: estimated rows vs. actual rows Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.137 tagged_above=0 required=5 tests=AWL, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200502/200 X-Sequence-Number: 10529 Hi, in the #postgresql-es channel someone shows me this: pgsql-7.4.5 + postgis=20 --- begin context --- CREATE TABLE calles ( gid int4 NOT NULL DEFAULT nextval('public.callesstgo_gid_seq'::text), nombre varchar, inicio int4, termino int4, comuna varchar, ciudad varchar, region numeric, pais varchar, the_geom geometry, id_comuna numeric, CONSTRAINT callesstgo_pkey PRIMARY KEY (gid), CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) =3D 'MULTILINESTRING'::text OR the_geom IS NULL), CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) =3D -1) )=20 WITH OIDS; =A0 CREATE INDEX idx_region_comunas ON calles USING btree (id_comuna, region); select count(*) from calles; 143902 --- end context --- =A0 Ok . here is the problem (BTW, the database has been analyzed just before this query was execured) explain analyze select * from calles where id_comuna =3D 92 and region=3D13;=20 QUERY PLAN Seq Scan on calles (cost=3D0.00..7876.53 rows=3D2610 width=3D279) (actual time=3D182.590..454.195 rows=3D4612 loops=3D1) Filter: ((id_comuna =3D 92::numeric) AND (region =3D 13::numeric)) Total runtime: 456.876 ms Why is this query using a seq scan rather than a index scan? i notice the diff between the estimated rows and actual rows (almost 2000). Can this affect the query plan? i think this is a problem of statistics, am i right? if so, what can be done? regards, Jaime Casanova