Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 761F59FB1D2 for ; Tue, 16 Jan 2007 23:01:53 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 02473-04-5 for ; Tue, 16 Jan 2007 23:00:42 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from out4.smtp.messagingengine.com (out4.smtp.messagingengine.com [66.111.4.28]) by postgresql.org (Postfix) with ESMTP id DFCC59FB1D3 for ; Tue, 16 Jan 2007 22:59:00 -0400 (AST) Received: from out1.internal (unknown [10.202.2.149]) by out1.messagingengine.com (Postfix) with ESMTP id 50E467D286; Tue, 16 Jan 2007 21:58:59 -0500 (EST) Received: from web1.messagingengine.com ([10.202.2.210]) by out1.internal (MEProxy); Tue, 16 Jan 2007 21:58:59 -0500 Received: by web1.messagingengine.com (Postfix, from userid 99) id 2A35F2A369; Tue, 16 Jan 2007 21:58:59 -0500 (EST) Message-Id: <1169002739.19758.1169636285@webmail.messagingengine.com> X-Sasl-Enc: B/etnNxRdv6jXQvcBVywQnOBlkpu6CwvVQYcCTrL7ykz 1169002739 From: "Jeremy Haile" To: "Chad Wagner" Cc: "Tom Lane" , pgsql-performance@postgresql.org Content-Disposition: inline Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset="ISO-8859-1" MIME-Version: 1.0 X-Mailer: MessagingEngine.com Webmail Interface References: <1168982580.8275.1169588211@webmail.messagingengine.com> <26412.1168983547@sss.pgh.pa.us> <1168986053.16393.1169598917@webmail.messagingengine.com> <81961ff50701161444l3f932755m5a6e15ad2d948130@mail.gmail.com> Subject: Re: PG8.2.1 choosing slow seqscan over idx scan In-Reply-To: <81961ff50701161444l3f932755m5a6e15ad2d948130@mail.gmail.com> Date: Tue, 16 Jan 2007 21:58:59 -0500 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/248 X-Sequence-Number: 22849 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" said: > On 1/16/07, Jeremy Haile 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/