Hi gurus et al ;)
I have a database with a couple of hundred tables. In this database one
table, "person", represents a user. person.userid is a primary key. To
this key there are about a hundred foreign keys spread out over
aproximately equaly many tables. When deleting a user I noticed a quite
big difference in time depending on how much data there are in the
foreign key-tables. After some investigation I concluded that for some
users and some tables the indices wheren't used when deleting, resulting
in longer run-times.
Here's an example:
select count(*) from login;
count
-------
96824
select count(*) from login where userid = 'patrik';
count
-------
608
select count(*) from login where userid = 'jennie';
count
-------
4211
explain delete from login where userid = 'patrik';
QUERY PLAN
---------------------------------------------------------------------------------
Index Scan using login_userid_idx on login (cost=0.00..237.06 rows=61
width=6)
Index Cond: (userid = 'patrik'::text)
explain delete from login where userid = 'jennie';
QUERY PLAN
-----------------------------------------------------------
Seq Scan on login (cost=0.00..2045.30 rows=3421 width=6)
Filter: (userid = 'jennie'::text)
What makes the planer choose seq scan for 'jennie', but not for
'patrik'? I also tested the following:
delete from login where userid = 'jennie';
DELETE 4211
Time: 508.94 ms
set enable_seqscan = false;
delete from login where userid = 'jennie';
DELETE 4211
Time: 116.92 ms
As you can see the index scan is almost 5 times faster, but still
postgres chooses to seq scan... Am I doing something wrong or is
postgres being stupid on me?
select version();
version
-------------------------------------------------------------------
PostgreSQL 7.3 on i386-portbld-freebsd4.7, compiled by GCC 2.95.4
I tried the same thing on a similar database running on 7.3.2 with the
same results.
Regards,
Patrik Kudo
