pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jaime Casanova <systemguards@gmail.com>
To: performance pgsql <pgsql-performance@postgresql.org>
Subject: estimated rows vs. actual rows
Date: Sun, 13 Feb 2005 16:27:45 -0500
Message-ID: <c2d9e70e0502131327c95e63d@mail.gmail.com> (raw)

Hi,

in the #postgresql-es channel someone shows me this:

pgsql-7.4.5 + postgis 

--- 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) =
'MULTILINESTRING'::text OR the_geom IS NULL),
  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = -1)
) 
WITH OIDS;
 
CREATE INDEX idx_region_comunas ON calles USING btree
  (id_comuna, region);

select count(*) from calles;
143902

--- end context ---
 
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 = 92 and region=13; 

QUERY PLAN Seq Scan on calles  (cost=0.00..7876.53 rows=2610
width=279) (actual time=182.590..454.195 rows=4612 loops=1)
  Filter: ((id_comuna = 92::numeric) AND (region = 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



view thread (6+ messages)  latest in thread

Message-ID: <c2d9e70e0502131327c95e63d@mail.gmail.com>
Permalink:  ../c2d9e70e0502131327c95e63d@mail.gmail.com/
Also on:    postgresql.org/message-id/c2d9e70e0502131327c95e63d@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: systemguards@gmail.com
  Subject: Re: estimated rows vs. actual rows
  In-Reply-To: <c2d9e70e0502131327c95e63d@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox