PhpBB 3.x query review
PhpBB 3.x query review
От:
Jean-Michel Pouré <jm@poure.com>
Дата:
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
Re: PhpBB 3.x query review
От:
Shelby Cain <alyandon@yahoo.com>
Дата:
----- Original Message ----
> From: Jean-Michel Pouré
> 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 optimization by adding indexes to this table. If you expect that table to eventually contain many thousands of entries then an 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
Re: PhpBB 3.x query review
От:
Shane Ambler <pgsql@Sheeky.Biz>
Дата:
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
Re: PhpBB 3.x query review
От:
"Scott Marlowe" <scott.marlowe@gmail.com>
Дата:
On Jan 7, 2008 11:06 AM, 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. 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.
