Re: PostgreSQL 7.4 runs slower than 7.3

On Tue, 21 Sep 2004 11:00:33 -0400
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "D'Arcy J.M. Cain" <darcy(at)druid(dot)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 null
certrans_active | boolean | default true
certificate_id | integer | not null
certype_id | character varying(1) | not null
certrans_date | timestamp(0) with time zone | not null default
('now'::text)::timestamp(6) with time zone
certrans_amount | numeric(14,2) | not null
stuser_id | integer | not null
operator_id | integer | not null
certrans_ref | text |
cash_out_id | integer |
certrans_void_ref | integer |
certrans_local_date | date | default
('now'::text)::date
certrans_local_time | time without time zone | default
('now'::text)::time(6) with time zone
channel_id | integer | default 0
loyalty_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(at)druid(dot)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.