Re: More full text index..

"Mitch Vincent" <mitch@venux.net> writes:
> EXPLAIN on a delete isn't very interesting..
> databasename=# explain delete from applicants_resumes where app_id=62908;
> NOTICE:  QUERY PLAN:

> Index Scan using app_resume_app_id_index on applicants_resumes
> (cost=0.00..3.70 rows=1 width=6)

I believe that doesn't tell you anything about triggers that might be
triggered during execution of the statement.  I think you are right that
the deletes issued by the trigger are the problem...

> Now this :

> query: delete from resumes_fti where id=86370016;
> ProcessQuery
> ! system usage stats:
> !       94.297058 elapsed 66.381692 user 24.776035 system sec
> !       [66.399740 user 24.785696 sys total]
> !       10926/8 [10926/8] filesystem blocks in/out
> !       0/30789 [0/31005] page faults/reclaims, 0 [0] swaps
> !       0 [0] signals rcvd, 0/0 [3/3] messages rcvd/sent
> !       186/1493 [189/1496] voluntary/involuntary context switches
> ! postgres usage stats:
> !       Shared blocks:      45945 read,         32 written, buffer hit rate
> = 3.24%
> !       Local  blocks:          0 read,          0 written, buffer hit rate
> = 0.00%
> !       Direct blocks:          0 read,          0 written

> Most of that is greek to me -- speaking of which, is there any place where
> these stats are explained a bit?

For the "system usage" stats, see man getrusage(2).  I think the other
thing you need to know is that the numbers in square brackets are total
getrusage for the current backend, the numbers before brackets are the
incremental usage for the current command.  These do count absolutely
everything including trigger activity.

The "postgres usage" stats are just I/O block request counts for the
shared and transaction-local buffer caches respectively ... but I forget
whether they are kernel I/O requests or logical I/O requests, ie which
side of the buffer cache they are counted on.
        regards, tom lane