I'm seeing some queries, possibly to do with using a UNIQUE index,
that have fast time reported by EXPLAIN ANALYZE but the actual time as
reported by \timing at 150ms+ higher. PostgreSQL 8.4.9
Simple example queries: http://paste.ubuntu.com/726131/
Table definitions: http://paste.ubuntu.com/726193/
Rewriting the query to use common table expressions worked around the
original problem: http://paste.ubuntu.com/726141/ ('fixed' version of
the original more complex query).
We also found this problem did not occur on one of our staging
systems, which had a default statistics target of 100. Lowering the
statistics on the relavant columns from 1000 to 100 and reanalyzing
made the overhead unnoticeable.
Thoughts on IRC was this might be a regression in 8.4.9, but I haven't
got earlier versions to test with at the moment. I was asked to obtain
some traces but have not been able to organize getting tools on a
suitable server yet.
--
Stuart Bishop <stuart@stuartbishop.net>
http://www.stuartbishop.net/
