pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
estimated rows vs. actual rows
6+ messages / 5 participants
[nested] [flat]

* estimated rows vs. actual rows
@ 2005-02-13 21:27 Jaime Casanova <systemguards@gmail.com>
  2005-02-13 21:41 ` Re: estimated rows vs. actual rows Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Jaime Casanova @ 2005-02-13 21:27 UTC (permalink / raw)
  To: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: estimated rows vs. actual rows
  2005-02-13 21:27 estimated rows vs. actual rows Jaime Casanova <systemguards@gmail.com>
@ 2005-02-13 21:41 ` Josh Berkus <josh@agliodbs.com>
  2005-02-14 03:18   ` Re: estimated rows vs. actual rows Jaime Casanova <systemguards@gmail.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Josh Berkus @ 2005-02-13 21:41 UTC (permalink / raw)
  To: Jaime Casanova <systemguards@gmail.com>; +Cc: pgsql-performance

Jaime,

> Why is this query using a seq scan rather than a index scan? 

Because it thinks a seq scan will be faster.

> i notice 
> the diff between the estimated rows and actual rows (almost 2000).

Yes, ANALYZE, and possibly increasing the column stats, should help that.

> Can this affect the query plan? i think this is a problem of
> statistics, am i right? if so, what can be done?

Well, if the estimate was accurate, PG would be even *more* likely to use a 
seq scan (more rows).

I think maybe you should establish whether a seq scan actually *is* faster?   
Perhaps do SET enable_seqscan = false and then re-run the query a few times?

-- 
Josh Berkus
Aglio Database Solutions
San Francisco



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: estimated rows vs. actual rows
  2005-02-13 21:27 estimated rows vs. actual rows Jaime Casanova <systemguards@gmail.com>
  2005-02-13 21:41 ` Re: estimated rows vs. actual rows Josh Berkus <josh@agliodbs.com>
@ 2005-02-14 03:18   ` Jaime Casanova <systemguards@gmail.com>
  2005-02-14 03:38     ` Re: estimated rows vs. actual rows Tom Lane <tgl@sss.pgh.pa.us>
  2005-02-14 03:45     ` Re: estimated rows vs. actual rows Mark Kirkwood <markir@coretech.co.nz>
  2005-02-14 12:41     ` Re: estimated rows vs. actual rows Christopher Browne <cbbrowne@acm.org>
  0 siblings, 3 replies; 6+ messages in thread

From: Jaime Casanova @ 2005-02-14 03:18 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance

On Sun, 13 Feb 2005 13:41:09 -0800, Josh Berkus <josh@agliodbs.com> wrote:
> Jaime,
> 
> > Why is this query using a seq scan rather than a index scan?
> 
> Because it thinks a seq scan will be faster.
> 
I will suggest him to probe with seq scans disabled.

But, IMHO, if the table has 143902 and it thinks will retrieve 2610
(almost 1.81% of the total). it won't be faster with an index?

i know, i will suggest him to probe to be sure. just an opinion.

regards,
Jaime Casanova



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: estimated rows vs. actual rows
  2005-02-13 21:27 estimated rows vs. actual rows Jaime Casanova <systemguards@gmail.com>
  2005-02-13 21:41 ` Re: estimated rows vs. actual rows Josh Berkus <josh@agliodbs.com>
  2005-02-14 03:18   ` Re: estimated rows vs. actual rows Jaime Casanova <systemguards@gmail.com>
@ 2005-02-14 03:38     ` Tom Lane <tgl@sss.pgh.pa.us>
  2 siblings, 0 replies; 6+ messages in thread

From: Tom Lane @ 2005-02-14 03:38 UTC (permalink / raw)
  To: Jaime Casanova <systemguards@gmail.com>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-performance

Jaime Casanova <systemguards@gmail.com> writes:
> But, IMHO, if the table has 143902 and it thinks will retrieve 2610
> (almost 1.81% of the total). it won't be faster with an index?

That's almost one row in fifty.  We don't know how wide the table is,
but it's certainly possible that there are order-of-a-hundred rows
on each page; in which case the indexscan is likely to hit every page.
Twice.  Not in sequence.  Only if the selected rows are pretty well
clustered in a small part of the table is this going to be a win
over a seqscan.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: estimated rows vs. actual rows
  2005-02-13 21:27 estimated rows vs. actual rows Jaime Casanova <systemguards@gmail.com>
  2005-02-13 21:41 ` Re: estimated rows vs. actual rows Josh Berkus <josh@agliodbs.com>
  2005-02-14 03:18   ` Re: estimated rows vs. actual rows Jaime Casanova <systemguards@gmail.com>
@ 2005-02-14 03:45     ` Mark Kirkwood <markir@coretech.co.nz>
  2 siblings, 0 replies; 6+ messages in thread

From: Mark Kirkwood @ 2005-02-14 03:45 UTC (permalink / raw)
  To: Jaime Casanova <systemguards@gmail.com>; +Cc: pgsql-performance

Jaime Casanova wrote:
> 
> But, IMHO, if the table has 143902 and it thinks will retrieve 2610
> (almost 1.81% of the total). it won't be faster with an index?
> 

Depends on how those 2610 rows are distributed amongst the 143902. The 
worst case scenario is each one of them in its own page. In that case 
you have to read 2610 *pages*, which is probably a significant 
percentage of the table.

You can find out this information from the pg_stats view (particularly 
the correlation column).


Mark



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: estimated rows vs. actual rows
  2005-02-13 21:27 estimated rows vs. actual rows Jaime Casanova <systemguards@gmail.com>
  2005-02-13 21:41 ` Re: estimated rows vs. actual rows Josh Berkus <josh@agliodbs.com>
  2005-02-14 03:18   ` Re: estimated rows vs. actual rows Jaime Casanova <systemguards@gmail.com>
@ 2005-02-14 12:41     ` Christopher Browne <cbbrowne@acm.org>
  2 siblings, 0 replies; 6+ messages in thread

From: Christopher Browne @ 2005-02-14 12:41 UTC (permalink / raw)
  To: pgsql-performance

After takin a swig o' Arrakan spice grog, systemguards@gmail.com (Jaime Casanova) belched out:
> On Sun, 13 Feb 2005 13:41:09 -0800, Josh Berkus <josh@agliodbs.com> wrote:
>> Jaime,
>> 
>> > Why is this query using a seq scan rather than a index scan?
>> 
>> Because it thinks a seq scan will be faster.
>> 
> I will suggest him to probe with seq scans disabled.
>
> But, IMHO, if the table has 143902 and it thinks will retrieve 2610
> (almost 1.81% of the total). it won't be faster with an index?

If the 2610 rows are scattered widely enough, it may be cheaper to do
a seq scan.

After all, with a seq scan, you read each block of the table's pages
exactly once.

With an index scan, you read index pages _and_ table pages, and may do
and redo some of the pages.

It sounds as though it's worth forcing the matter and trying it both
ways and comparing them.  Don't be surprised if the seq scan is in
fact faster...
-- 
select 'cbbrowne' || '@' || 'gmail.com';
http://cbbrowne.com/info/emacs.html
When aiming for the common denominator, be prepared for the occasional
division by zero.



^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2005-02-14 12:41 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-13 21:27 estimated rows vs. actual rows Jaime Casanova <systemguards@gmail.com>
2005-02-13 21:41 ` Josh Berkus <josh@agliodbs.com>
2005-02-14 03:18   ` Jaime Casanova <systemguards@gmail.com>
2005-02-14 03:38     ` Tom Lane <tgl@sss.pgh.pa.us>
2005-02-14 03:45     ` Mark Kirkwood <markir@coretech.co.nz>
2005-02-14 12:41     ` Christopher Browne <cbbrowne@acm.org>

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