Jean-Michel Pouré wrote:
Dear friends,
I am reviewing some of PhpBB 3.x queries.
This allows me to learn more about PostgreSQL.
The thread can be read here:
http://area51.phpbb.com/phpBB/viewtopic.php?f=3&t=29260
Do not hesitate to post your review there.
In pg_tables, I saw that there was an extensive use of sequential scans
in phpbb_banlist, a small table of 60 rows.
explain analyse
SELECT ban_ip, ban_userid, ban_email, ban_exclude, ban_give_reason,
ban_end
FROM phpbb_banlist
WHERE ban_email = '' AND (ban_userid = 100394 OR ban_ip <> '')
Seq Scan on phpbb_banlist (cost=0.00..1.51 rows=13 width=33) (actual
time=0.013..0.026 rows=19 loops=1)
Filter: (((ban_email)::text = ''::text) AND ((ban_userid = 100394) OR
((ban_ip)::text <> ''::text)))
Total runtime: 0.063 ms
I set indexes on ban_email, ban_userid and ban_ip.
But the query plan is still the same.
First off with a runtime of 0.063ms I don't think you have an issue.
I assume you have run analyse after changing indexes etc.
The planner chooses the most optimal plan it can find to achieve the end
result. This may or may not use indexes to get the end result.
The fact that the table has 60 rows would indicate that the time to do a
seqscan is less than to do an index scan plus read the data needed. If
this is a frequent query then the table would most likely be cached in
RAM anyway.
Doing the compare on text fields would also weigh things toward seqscan.
If you wanted to test this out try adding 1000 rows to phpbb_banlist,
analyse then explain the query again and see if it goes to the index then.
If you wanted to experiment enough you should be able to find a "sweet
spot" of x rows where it will change between index and seq scans.
--
Shane Ambler
pgSQL (at) Sheeky (dot) Biz
Get Sheeky @ http://Sheeky.Biz
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
