pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedIndex selection on a large table
2+ messages / 2 participants
[nested] [flat]
* Index selection on a large table
@ 2000-07-25 23:03 Michael Richards <michael@fastmail.ca>
2000-07-25 23:30 ` Re: [SQL] Index selection on a large table Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Michael Richards @ 2000-07-25 23:03 UTC (permalink / raw)
To: pgsql-sql; pgsql-bugs@postgresql.org
Hi.
I believe this to be a bug, but I am submitting it to the SQL list as
well in the case I overlooked something.
I'm running
Postgres 7.0.2 on FreeBSD 4.0-STABLE/Intel
compiled with [gcc version 2.95.2 19991024 (release)]
I've got this interesting problem where the query plan is not what I
expect. Every month we log details of users logging into their
webmail accounts. Every week before the database is vacuumed the
oldest week's entries are removed. The table can be quite large. Here
is the relevant parts of its structure:
Table "logins"
Attribute | Type | Modifier
-----------+-------------+----------
loginid | varchar(16) |
ip | varchar(15) |
[...]
Indices: logins_ip_idx,
logins_loginid_idx
The indexes are as follows:
Index "logins_ip_idx"
Attribute | Type
-----------+-------------
ip | varchar(15)
btree
Index "logins_loginid_idx"
Attribute | Type
-----------+-------------
loginid | varchar(16)
btree
Size of the table:
fastmail=> select count(*) from logins;
count
---------
1082564
(1 row)
Now here is a query plan from a selection using loginid:
explain select * from logins where loginid='michael';
NOTICE: QUERY PLAN:
Index Scan using logins_loginid_idx on logins (cost=0.00..500.57
rows=130 width=48)
As expected it uses the logins_loginid_idx to select the rows that
match loginid='michael';
Now I should note that I store the IP's as type varchar(15).
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)
This one decides to ignore the fact that IP is indexed (and the table
was vacuumed) and it does a slow-assed sequential scan through a
million or so rows to pick out (in this case 3 matching rows).
Just to be sure, I re-vacuumed the table and tried the questionable
query again with the same results. Here is the vacuum output in case
it helps:
NOTICE: --Relation logins--
NOTICE: Pages 11717: Changed 1, reaped 0, Empty 0, New 0; Tup
1082580: Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 64, MaxLen
92; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU
2.28s/15.38u sec.
NOTICE: Index logins_ip_idx: Pages 4550; Tuples 1082580. CPU
0.78s/2.65u sec.
NOTICE: Index logins_loginid_idx: Pages 3881; Tuples 1082580. CPU
0.62s/2.67u sec.
VACUUM
-Michael
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [SQL] Index selection on a large table
2000-07-25 23:03 Index selection on a large table Michael Richards <michael@fastmail.ca>
@ 2000-07-25 23:30 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2000-07-25 23:30 UTC (permalink / raw)
To: Michael Richards <michael@fastmail.ca>; +Cc: pgsql-sql; pgsql-bugs@postgresql.org
"Michael Richards" <michael@fastmail.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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2000-07-25 23:30 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-07-25 23:03 Index selection on a large table Michael Richards <michael@fastmail.ca>
2000-07-25 23:30 ` Tom Lane <tgl@sss.pgh.pa.us>
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