index usage (and foreign keys/triggers) - Mailing list pgsql-general

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