Re: PostgreSQL 7.4 runs slower than 7.3 - Mailing list pgsql-hackers

On Tue, 21 Sep 2004 11:00:33 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "D'Arcy J.M. Cain" <darcy@druid.net> writes:
> > I had previously mentioned that queries run much different depending
> > on whether a VACUUM ANALYZE is done or just a plain ANALYZE.
> 
> The problem appears to be that it's picking the wrong index to use for
> the second part of the query (the certrans_active = 'f' part).  You
> have not shown us enough info to guess why though.

Well, I guess I am looking for is hints on what other information you
might need.  The problem is that both of these versions are running the
same schema, the same data and the same indexes.  In fact, we are
running software to keep both databases in sync.

As it turns out, if we VACUUM ANALYZE or ANALYZE the 7.3 database we
lose the quick access that we had seen.  We never get back to 9 again. 
I am not sure what that means.  It could have been a false reading
except that we definitely say a better result when it was 9.

If it helps, here is the table definition.  There are also a bunch of
rules on insert, update and delete but I don't imagine those have any
effect on selects.  Is there anything else I can give you?
                             Table "public.certrans"      Column        |            Type             |            
Modifiers
---------------------+-----------------------------+-------------------
-------certrans_id         | integer                     | not nullcertrans_active     | boolean                     |
defaulttruecertificate_id      | integer                     | not nullcertype_id          | character varying(1)
| not nullcertrans_date       | timestamp(0) with time zone | not null default
 
('now'::text)::timestamp(6) with time zonecertrans_amount     | numeric(14,2)               | not nullstuser_id
 | integer                     | not nulloperator_id         | integer                     | not nullcertrans_ref
| text                        | cash_out_id         | integer                     | certrans_void_ref   | integer
             | certrans_local_date | date                        | default
 
('now'::text)::datecertrans_local_time | time without time zone      | default
('now'::text)::time(6) with time zonechannel_id          | integer                     | default 0loyalty_amount      |
numeric(14,2)              | default 0
 
Indexes:   "certrans_pkey" primary key, btree (certrans_id)   "certrans_cash_out_id" btree (cash_out_id)
"certrans_certificate_id"btree (certificate_id)   "certrans_certrans_active" btree (certrans_active)
"certrans_certrans_date"btree (certrans_date)   "certrans_certrans_local_date" btree (certrans_local_date)
"certrans_certrans_void_ref"btree (certrans_void_ref)   "certrans_certype_id" btree (certype_id)
"certrans_channel_id"btree (channel_id)   "certrans_oid" btree (oid)   "certrans_operator_id" btree (operator_id)
"certrans_sortkey"btree (certificate_id, certrans_id)   "certrans_stuser_id" btree (stuser_id)
 
Foreign-key constraints:   "$1" FOREIGN KEY (certificate_id) REFERENCES
certificate(certificate_id)


-- 
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.