pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Jeremy Haile <jhaile@fastmail.fm>
To: Chad Wagner <chad.wagner@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: PG8.2.1 choosing slow seqscan over idx scan
Date: Tue, 16 Jan 2007 21:58:59 -0500
Message-ID: <1169002739.19758.1169636285@webmail.messagingengine.com> (raw)
In-Reply-To: <81961ff50701161444l3f932755m5a6e15ad2d948130@mail.gmail.com>
References: <1168982580.8275.1169588211@webmail.messagingengine.com>
	<26412.1168983547@sss.pgh.pa.us>
	<1168986053.16393.1169598917@webmail.messagingengine.com>
	<81961ff50701161444l3f932755m5a6e15ad2d948130@mail.gmail.com>

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.wagner@gmail.com> said:
> On 1/16/07, Jeremy Haile <jhaile@fastmail.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/



view thread (21+ messages)  latest in thread

Message-ID: <1169002739.19758.1169636285@webmail.messagingengine.com>
Permalink:  ../1169002739.19758.1169636285@webmail.messagingengine.com/
Also on:    postgresql.org/message-id/1169002739.19758.1169636285@webmail.messagingengine.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: jhaile@fastmail.fm, chad.wagner@gmail.com, tgl@sss.pgh.pa.us
  Subject: Re: PG8.2.1 choosing slow seqscan over idx scan
  In-Reply-To: <1169002739.19758.1169636285@webmail.messagingengine.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox