pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Kelly Burkhart <kelly@tradebotsystems.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: optimizer tuning/forcing correct index use
Date: Tue, 19 Mar 2002 11:12:10 -0600
Message-ID: <02031911121009.00735@krbdev> (raw)
In-Reply-To: <29145.1016551722@sss.pgh.pa.us>
References: <02031907224907.00735@krbdev>
	<29145.1016551722@sss.pgh.pa.us>

On Tuesday 19 March 2002 09:28 am, Tom Lane wrote:
> Kelly Burkhart <kelly@tradebotsystems.com> writes:
> > The critical part is driving the fill table query from a sequential scan
> > rather than a scan of fill_ak2.
>
> Have you done an ANALYZE or VACUUM ANALYZE lately?  If so, what do you
> get from
>
> select * from pg_stats where tablename = 'fill';
> select * from pg_class where relname = 'fill';
>
> Offhand I am guessing that the table is fairly well ordered by fill_ts
> and the planner is underestimating the effects of this.  There is a
> provision in there to try to account for data ordering, but it's new
> code in 7.2 and doubtless still needs refinement.

I've attached the results of those queries.

The table was loaded in one week chunks, mostly but not entirely in order.  
11/2001-3/2002 was loaded first in order, then 1/2001-10/2001 was loaded some 
time later.  Each dump file is probably in order, if you trust that SQL 
Server used the index order in the query (which you probably can).

In the future, when/if this database becomes production, each week we will 
nuke all rows older than 1 year (or maybe 2 years... who knows).  So after a 
point, the db should grow little.

-K

Attachments:

stats_out.txt (text/plain, 20.7K) download tablename | attname | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation -----------+-----------------------+-----------+-----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------- fill | fill_id | 0 | 4 | -1 | | | {1426,842948,1633637,2471428,3298985,4098548,4855326,5575535,6415132,7157589,7924076} | -0.546947 fill | last_update_ts | 0 | 8 | 153524 | {"2001-08-13 08:49:19-05","2001-08-13 08:49:41-05","2001-10-10 09:04:33-05","2001-10-22 15:33:26-05","2001-10-31 14:05:43-06","2002-01-07 13:35:48-06"} | {0.000666667,0.000666667,0.000666667,0.000666667,0.000666667,0.000666667} | {"2001-01-10 14:13:01-06","2001-07-10 13:14:22-05","2001-09-10 10:05:20-05","2001-10-15 10:28:32-05","2001-11-02 08:34:35-06","2001-11-28 09:16:26-06","2001-12-20 09:54:17-06","2002-01-15 08:38:42-06","2002-02-04 09:54:44-06","2002-02-25 09:06:22-06","2002-03-08 14:43:10-06"} | -0.546947 fill | last_username | 0 | 9 | 17 | {TRADEBOT,brian,mike,fred,derrek} | {0.283333,0.0986667,0.0866667,0.08,0.079} | {brim,james,kirk,kirk,lance,marc,matt,mump,rob,scott,steve} | -0.316908 fill | disabled | 0 | 2 | 2 | {0,1} | {0.998333,0.00166667} | | 0.99153 fill | order_id | 0 | 4 | -1 | | | {14790,7124068,16718341,18680017,25932066,34116069,43743780,52423239,62596503,74091534,83109496} | -0.546947 fill | symbol_full_name | 0 | 12 | 774 | {ISLD:QQQ,ISLD:QCOM,ISLD:BRCM,ISLD:QLGC,ISLD:AMAT,ISLD:BRCD,ISLD:MSFT,ISLD:KLAC,ISLD:SPY,ISLD:VRTS} | {0.292333,0.0553333,0.0493333,0.044,0.0413333,0.0343333,0.0333333,0.0316667,0.0283333,0.0276667} | {ARCA:AMGN,ISLD:BGEN,ISLD:CMVT,ISLD:EMLX,ISLD:INTC,ISLD:LLTC,ISLD:NVDA,ISLD:NVLS,ISLD:RFMD,ISLD:VRSN,ISLD:YHOO} | 0.149493 fill | buy_or_sell | 0 | 5 | 3 | {S,B,5} | {0.510667,0.487,0.00233333} | | 0.494946 fill | fill_quantity | 0 | 4 | 5595 | {100,200,300,500,400,600,800,700,1000,900} | {0.289333,0.173667,0.118,0.11,0.0553333,0.0273333,0.023,0.0226667,0.0226667,0.0123333} | {1,18,38,50,70,93,126,160,230,350,1500} | 0.162993 fill | fill_price | 0 | 4 | 131483 | {40.669,45.5,34.718,34.809,35.561,37.16,39.25,39.261,39.318,39.62} | {0.00133333,0.00133333,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001} | {10.251,27.909,33.911,36.261,38.361,40.438,42.85,46.761,53.139,60.19,218.156} | -0.134914 fill | fill_ts | 0 | 8 | 152655 | {"2001-10-22 15:28:07-05","2001-10-22 15:28:16-05","2001-10-22 12:43:28-05","2001-08-13 08:49:19-05","2001-08-13 08:49:41-05","2001-09-25 16:13:41-05","2001-10-10 09:04:33-05","2001-10-22 14:50:05-05","2001-10-31 14:05:43-06","2002-01-07 13:35:48-06"} | {0.002,0.002,0.001,0.000666667,0.000666667,0.000666667,0.000666667,0.000666667,0.000666667,0.000666667} | {"2001-01-10 14:13:01-06","2001-07-10 13:05:01-05","2001-09-10 09:26:08-05","2001-10-15 09:01:54-05","2001-11-02 09:49:58-06","2001-11-28 09:36:33-06","2001-12-21 08:38:03-06","2002-01-15 09:34:59-06","2002-02-04 11:34:20-06","2002-02-25 09:39:55-06","2002-03-08 14:43:10-06"} | -0.546947 fill | environment | 0 | 5 | 1 | {L} | {1} | | 1 fill | automated | 0 | 5 | 2 | {A,M} | {0.911667,0.0883333} | | 0.921679 fill | trader_username | 0 | 8 | 18 | {brian,derrek,mike,james,rob,fred,kirk} | {0.136333,0.109333,0.101,0.097,0.0916667,0.0816667,0.0803333} | {brim,brim,dave,lance,lance,marc,matt,matt,mump,steve,steve} | 0.02795 fill | ordermgr_username | 0 | 8 | 18 | {brian,derrek,mike,james,rob,fred,kirk} | {0.136333,0.109333,0.101,0.097,0.0916667,0.0816667,0.0803333} | {brim,brim,dave,lance,lance,marc,matt,matt,mump,steve,steve} | 0.02795 fill | account_number | 0 | 9 | 265 | {BRIAN3,BRIAN1,MIKE2,FRED2,KIRK3,derrek1,JAMES1,LANCE1,MATT2,MIKE1} | {0.0663333,0.065,0.0606667,0.055,0.0483333,0.041,0.0346667,0.0316667,0.0313333,0.0313333} | {DAVE1,DERREK2,JAMES2,MARC,MATT1,STEVE2,TBKELLY,TBROB,james1,scott1,tbrob} | 0.0977319 fill | clearing_account_id | 0 | 4 | 36 | {202,853,51,701,152,851,151,854,351,3} | {0.0816667,0.0663333,0.0626667,0.061,0.059,0.0556667,0.0526667,0.0483333,0.04,0.039} | {1,2,251,401,451,551,852,1051,1151,1201,1551} | -0.363017 fill | model_name | 0.716667 | 14 | 6772 | {KIRK1,IQQQHedge,IPAIR3--1,SS_TOP10,TBMODEL,A_LIST_NORMAL,IQQQBRIM,BLACKWOOD-SCRAPS,IPAIR3--4,IQQQ1} | {0.0113333,0.00666667,0.006,0.006,0.006,0.00566667,0.005,0.00466667,0.00466667,0.00466667} | {A-J,BLACKWOOD_TOP-9,DB_E-K-9,IPAIR2_TWO_nqm1_brcm_vrts,IPAIR3-SHORTAVS,IQQQQ1,MATTHEWSTOCKS2,QQQ-1,SS_TOP_NEW,WHITEY108b,tbModel} | -0.0545805 fill | blotter_id | 0 | 4 | 36293 | {0,1,168360,61654,125706,165052,165406,168308,507,30255} | {0.703333,0.0133333,0.00133333,0.001,0.001,0.001,0.001,0.001,0.000666667,0.000666667} | {8363,51455,79592,103560,116252,131403,142009,147911,156170,163404,168362} | 0.901327 fill | fill_trade_dt | 0 | 8 | 109724 | {"2001-08-13 00:00:00-05","2001-10-04 00:00:00-05","2001-10-11 00:00:00-05","2001-09-05 00:00:00-05","2001-09-06 00:00:00-05","2001-10-05 00:00:00-05","2001-09-10 00:00:00-05","2001-10-22 00:00:00-05","2001-09-20 00:00:00-05","2001-09-21 00:00:00-05"} | {0.00933333,0.007,0.00666667,0.00633333,0.00633333,0.00633333,0.006,0.006,0.00566667,0.00533333} | {"2001-01-10 00:00:00-06","2001-07-05 00:00:00-05","2001-09-24 00:00:00-05","2001-10-23 13:44:19-05","2001-11-09 00:00:00-06","2001-12-05 09:00:49-06","2002-01-03 14:19:05-06","2002-01-17 09:07:23-06","2002-02-05 13:32:34-06","2002-02-25 13:52:58-06","2002-03-08 14:43:10-06"} | -0.546936 fill | daily_processing_done | 0 | 2 | 1 | {0} | {1} | | 1 fill | immediate_fill | 0 | 2 | 2 | {0,1} | {0.679,0.321} | | 0.573131 fill | vendor_execution_id | 0.0953333 | 10 | 139034 | {19845,286746,30611,440463} | {0.000666667,0.000666667,0.000666667,0.000666667} | {0000d4e1.3b49c119.01.01,150015,2010054,252690,309575,35424918,400668,482646,54931766,649220,998636} | -0.2722 fill | ecn_fees | 0 | 4 | 3769 | {0,-0.1,-0.2,-0.3,0.25,-0.5,0.75,0.5,1.25,-0.4} | {0.523,0.0843333,0.0513333,0.0353333,0.0316667,0.025,0.0243333,0.0206667,0.017,0.0153333} | {-1,-0.8,-0.6,-0.25,-0.11,-0.07,-0.01,0.21,0.68,1.5,2.5} | 0.420297 fill | sec_fees | 0 | 4 | 13822 | {0,0.06,0.07,0.09,0.08,0.17,0.1,0.11,0.05,0.16} | {0.754,0.0146667,0.012,0.00933333,0.008,0.00766667,0.00666667,0.006,0.00566667,0.00433333} | {0.00244014,0.04,0.113247,0.14,0.19,0.229464,0.278375,0.32,0.43,0.54,1.55671} | 0.333208 fill | clearing_fees | 0 | 4 | 1374 | {0,0.1,0.2,0.3,0.5,0.4,0.6,0.7,0.9,0.8} | {0.646333,0.0966667,0.053,0.0476667,0.0296667,0.0206667,0.0183333,0.014,0.0106667,0.01} | {0.004,0.02,0.04,0.05,0.079,0.11,0.13,0.18,0.27,0.65,1} | 0.139854 fill | brokerage_fees | 0 | 4 | 2488 | {0,0.67,0.335,0.34,1.675,1.01,0.25,1.005,1.34,0.13} | {0.875667,0.0213333,0.018,0.015,0.0113333,0.00833333,0.006,0.00533333,0.00366667,0.00233333} | {0.01,0.07,0.125,0.18,0.28,0.375,0.38,0.5159,0.87,2.01,3.35} | 0.631645 (26 rows) relname | reltype | relowner | relam | relfilenode | relpages | reltuples | reltoastrelid | reltoastidxid | relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules | relhassubclass | relacl ---------+---------+----------+-------+-------------+----------+-------------+---------------+---------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+-------- fill | 16730 | 100 | 0 | 16729 | 201089 | 7.56234e+06 | 0 | 0 | t | f | r | 26 | 0 | 0 | 0 | 0 | 0 | t | f | f | f | (1 row)

view thread (6+ messages)  latest in thread

Message-ID: <02031911121009.00735@krbdev>
Permalink:  ../02031911121009.00735@krbdev/
Also on:    postgresql.org/message-id/02031911121009.00735@krbdev

 · 

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-sql@postgresql.org
  Cc: kelly@tradebotsystems.com, tgl@sss.pgh.pa.us
  Subject: Re: optimizer tuning/forcing correct index use
  In-Reply-To: <02031911121009.00735@krbdev>

* 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