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
copy link · copy postgr.es
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