Обсуждение: PhpBB 3.x query review

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.

Any idea?

Kind regards,
Jean-Michel

On Jan 7, 2008 11:06 AM, Jean-Michel Pouré <jm@poure.com> 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.

What makes you think a seq scan is bad here? Have you compare the
output of explain analyze of the select with enable_seqscan on and
off?  I'm willing to bet seqscan is a win here.  60 rows probably fits
in one page, maybe two or three.  I can't imagine the random access of
a index lookups beating sequentially scanning a few pages of data.

----- Original Message ----
> From: Jean-Michel Pouré <jm@poure.com>
> To: pgsql-general@postgresql.org
> Sent: Monday, January 7, 2008 11:06:09 AM
> Subject: [GENERAL] PhpBB 3.x query review
>
>
> I set indexes on ban_email, ban_userid and ban_ip.
> But the query plan is still the same.
>
> Any idea?
>

There is hardly any data in the table (~133 rows per page by my calculation) so the planner is choosing
to do a direct sequential scan instead of using an indirect lookup through an
index because it is faster.  Considering the query returned in under 0.1ms I think you may be engaging in premature
optimizationby adding indexes to this table.  If you expect that table to eventually contain many thousands of entries
thenan index might be prudent. 


Regards,

Shelby Cain






      ____________________________________________________________________________________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile.  Try it now.  http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ

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