Skip site navigation (1) Skip section navigation (2)

Re: PG8.2.1 choosing slow seqscan over idx scan

From: "Jeremy Haile" <jhaile(at)fastmail(dot)fm>
To: "Chad Wagner" <chad(dot)wagner(at)gmail(dot)com>
Cc: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-performance(at)postgresql(dot)org
Subject: Re: PG8.2.1 choosing slow seqscan over idx scan
Date: 2007-01-17 02:58:59
Message-ID: 1169002739.19758.1169636285@webmail.messagingengine.com (view raw or flat)
Thread:
Lists: pgsql-performance
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/

In response to

Responses

pgsql-performance by date

Next:From: Chad WagnerDate: 2007-01-17 04:52:39
Subject: Re: PG8.2.1 choosing slow seqscan over idx scan
Previous:From: Chad WagnerDate: 2007-01-16 22:44:53
Subject: Re: PG8.2.1 choosing slow seqscan over idx scan

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group