Re: PG8.2.1 choosing slow seqscan over idx scan

["Jeremy Haile" <jhaile(at)fastmail(dot)fm>]

Hey Chad,

The table is heavily inserted and deleted from.  Recently I had done a
very large delete.

Here is the results of the query you sent me: (sorry it's hard to read)

"dcms_dim_id";0;4;755;-0.00676181
"transaction_fact_id";0;4;-1;-0.194694
"failed";0;4;2;0.964946
"van16";0;23;145866;0.00978649
"vendor_response";0.9942;43;9;0.166527
"transaction_id";0;4;-1;-0.199583
"transaction_date";0;8;172593;-0.194848
"serial_number";0.0434667;16;53311;0.0713039
"merchant_dim_id";0;4;105;0.299335
"comment";0.0052;29;7885;0.0219167
"archived";0;1;2;0.84623
"response_code";0.9942;4;3;0.905409
"transaction_source";0;4;2;0.983851
"location_dim_id";0;4;86;0.985384
"success";0;4;2;0.981072

Just curious - what does that tell us?

Jeremy Haile

On Tue, 16 Jan 2007 17:44:53 -0500, "Chad Wagner"
<chad(dot)wagner(at)gmail(dot)com> said:
> On 1/16/07, Jeremy Haile <jhaile(at)fastmail(dot)fm> wrote:
> >
> > Even if unrelated, do you think disk fragmentation would have negative
> > effects?  Is it worth trying to defragment the drive on a regular basis
> > in Windows?
> >
> 
> Out of curiosity, is this table heavily updated or deleted from?  Perhaps
> there is an unfavorable "correlation" between the btree and data?  Can
> you
> dump the results of
> 
> select attname, null_frac, avg_width, n_distinct, correlation from
> pg_stats
> where tablename = 'transaction_facts'
> 
> 
> 
> 
> -- 
> Chad
> http://www.postgresqlforums.com/