Re: PhpBB 3.x query review

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