I am looking at the statics table in PostgreSQL, to see when the database does sequential scans.
Do :
Code: Select all
select * from pg_tables
I found a query in my logs, that may be responsible:
PostgreSQL will make an extensive sequential scan on disc: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 <> '')
Code: Select all
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
The indexes are not used because Phpbb indexes are set on pair values.
Presently, we should have:
Code: Select all
CREATE INDEX phpbb_banlist_ban_email
ON phpbb_banlist
USING btree
(ban_email, ban_exclude);
Code: Select all
CREATE INDEX phpbb_banlist_ban_email
ON phpbb_banlist
USING btree
(ban_email);
Code: Select all
CREATE INDEX phpbb_banlist_ban_exclude
ON phpbb_banlist
USING btree
(ban_exclude);
Code: Select all
CREATE INDEX phpbb_banlist_ban_ip
ON phpbb_banlist
USING btree
(ban_ip, ban_exclude);
Code: Select all
CREATE INDEX phpbb_banlist_ban_ip
ON phpbb_banlist
USING btree
(ban_ip);
Code: Select all
CREATE INDEX phpbb_banlist_ban_user
ON phpbb_banlist
USING btree
(ban_userid, ban_exclude);
Code: Select all
CREATE INDEX phpbb_banlist_ban_user
ON phpbb_banlist
USING btree
(ban_userid);
Any explaination? Without explaination, I will post on postgresql mailing list to learn why so many disc access is performed.
I suspect that my shared memory is not large enough compared to the database size.