On Tue, 21 Sep 2004 11:00:33 -0400 Tom Lane <[EMAIL PROTECTED]> wrote: > "D'Arcy J.M. Cain" <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> | 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. ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org