Skip site navigation (1) Skip section navigation (2)

Re: [SQL] Index selection on a large table

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Michael Richards" <michael(at)fastmail(dot)ca>
Cc: pgsql-sql(at)postgresql(dot)org, pgsql-bugs(at)postgresql(dot)org
Subject: Re: [SQL] Index selection on a large table
Date: 2000-07-25 23:30:39
Message-ID: 8873.964567839@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-sql
"Michael Richards" <michael(at)fastmail(dot)ca> writes:
> The following query yeilds the questionable query plan:

> explain select * from logins where ip='38.30.141.44';
> NOTICE:  QUERY PLAN:
> Seq Scan on logins  (cost=0.00..25248.51 rows=11602 width=48)

If there really were 11602 matching rows, the seq scan likely would be
the way to go.  So the issue here is poor selectivity estimation.

You said you did a VACUUM, but you didn't mention if you'd ever done
a VACUUM ANALYZE.  That looks to me like it's using the default 1%
selectivity estimate, which is what you'd get if you hadn't ever
done an ANALYZE.

If you have done one, what do you get from

select attname,attdisbursion,s.*
from pg_statistic s, pg_attribute a, pg_class c
where starelid = c.oid and attrelid = c.oid and staattnum = attnum
and relname = 'logins';

?

			regards, tom lane

In response to

pgsql-bugs by date

Next:From: Andriy I PilipenkoDate: 2000-07-26 06:24:49
Subject: Index selection bug
Previous:From: Michael RichardsDate: 2000-07-25 23:03:38
Subject: Index selection on a large table

pgsql-sql by date

Next:From: Bernie HuangDate: 2000-07-25 23:34:19
Subject: Change attribute of a column
Previous:From: Tom LaneDate: 2000-07-25 23:05:27
Subject: Re: pg_dump

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group