pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedoptimizer tuning/forcing correct index use
6+ messages / 2 participants
[nested] [flat]
* optimizer tuning/forcing correct index use
@ 2002-03-15 16:03 Kelly Burkhart <kelly@tradebotsystems.com>
0 siblings, 0 replies; 6+ messages in thread
From: Kelly Burkhart @ 2002-03-15 16:03 UTC (permalink / raw)
To: pgsql-sql
Greetings,
I have a query that refuses to use a sensible index.
Some info first:
Version: PostgreSQL v 7.2
OS: SuSE Linux 7.2 w/Kernel 2.4.14
HW: Dual Athlon MP1800, 768MB RAM
DB exists on a single fast SCSI drive.
DB was loaded with a copy of our production system data (currently
running on a SQLServer database). 'vacuumdb -a -z' was run after
load, no rows have been added or deleted since vacuum.
Here is the query:
explain analyze
select
t.tb_order_number, f.account_number, f.clearing_account_id,
f.symbol_full_name, f.fill_quantity, f.buy_or_sell,
f.trader_username, f.fill_price, f.fill_quantity, f.fill_ts,
f.last_update_ts, s.symbol_type, s.base_symbol_full_name,
s.symbol_name, ca.clearing_account_number, ca.clearing_firm,
co.tick_value
from
fill f,
tb_order t,
symbol s,
clearing_account ca,
contract co
where
f.fill_ts > '2002-02-06 00:00:00' and
f.fill_ts <= '2002-02-08 23:59:59' and
t.order_id = f.order_id and
s.full_name = f.symbol_full_name and
co.contract_name = s.contract_name and
ca.clearing_account_id = f.clearing_account_id and
s.symbol_name not in ('ISLD:TESTA', 'ISLD:TESTB', 'ISLD:TESTC');
Total number of rows in the tables of the join:
fill : 7674725
tb_order : 20059204
symbol : 559
clearing_account : 57
contract : 13
When the time range (defined by fill_ts) is under 48 hours, the
following execution plan is used:
Nested Loop (cost=35.98..477477.54 rows=37403 width=170) (actual time=6.38..7868.65 rows=143582 loops=1)
-> Hash Join (cost=35.98..256958.14 rows=37403 width=152) (actual time=6.21..3937.04 rows=143582 loops=1)
-> Hash Join (cost=1.71..256229.53 rows=45364 width=90) (actual time=0.50..2685.68 rows=143582 loops=1)
-> Index Scan using fill_ak2 on fill f (cost=0.00..255314.58 rows=69239 width=66) (actual time=0.26..1875.38 rows=143582 loops=1)
-> Hash (cost=1.57..1.57 rows=57 width=24) (actual time=0.20..0.20 rows=0 loops=1)
-> Seq Scan on clearing_account ca (cost=0.00..1.57 rows=57 width=24) (actual time=0.05..0.14 rows=57 loops=1)
-> Hash (cost=32.89..32.89 rows=547 width=62) (actual time=5.66..5.66 rows=0 loops=1)
-> Hash Join (cost=1.16..32.89 rows=547 width=62) (actual time=0.21..4.73 rows=559 loops=1)
-> Seq Scan on symbol s (cost=0.00..20.78 rows=547 width=48) (actual time=0.05..2.43 rows=559 loops=1)
-> Hash (cost=1.13..1.13 rows=13 width=14) (actual time=0.11..0.11 rows=0 loops=1)
-> Seq Scan on contract co (cost=0.00..1.13 rows=13 width=14) (actual time=0.06..0.08 rows=13 loops=1)
-> Index Scan using order_pk on tb_order t (cost=0.00..5.88 rows=1 width=18) (actual time=0.02..0.02 rows=1 loops=143582)
Total runtime: 7934.64 msec
Somewhere above 48 hours the plan changes to this:
Nested Loop (cost=35.98..644155.97 rows=56104 width=170) (actual time=48504.60..128512.95 rows=196483 loops=1)
-> Hash Join (cost=35.98..313376.24 rows=56104 width=152) (actual time=48450.25..121546.48 rows=196483 loops=1)
-> Hash Join (cost=1.71..312300.45 rows=68045 width=90) (actual time=48444.62..119822.68 rows=196483 loops=1)
-> Seq Scan on fill f (cost=0.00..310928.88 rows=103859 width=66) (actual time=48444.38..118737.56 rows=196483 loops=1)
-> Hash (cost=1.57..1.57 rows=57 width=24) (actual time=0.19..0.19 rows=0 loops=1)
-> Seq Scan on clearing_account ca (cost=0.00..1.57 rows=57 width=24) (actual time=0.05..0.13 rows=57 loops=1)
-> Hash (cost=32.89..32.89 rows=547 width=62) (actual time=5.57..5.57 rows=0 loops=1)
-> Hash Join (cost=1.16..32.89 rows=547 width=62) (actual time=0.21..4.64 rows=559 loops=1)
-> Seq Scan on symbol s (cost=0.00..20.78 rows=547 width=48) (actual time=0.05..2.34 rows=559 loops=1)
-> Hash (cost=1.13..1.13 rows=13 width=14) (actual time=0.11..0.11 rows=0 loops=1)
-> Seq Scan on contract co (cost=0.00..1.13 rows=13 width=14) (actual time=0.07..0.09 rows=13 loops=1)
-> Index Scan using order_pk on tb_order t (cost=0.00..5.88 rows=1 width=18) (actual time=0.03..0.03 rows=1 loops=196483)
Total runtime: 128598.23 msec
The critical part is driving the fill table query from a sequential scan
rather than a scan of fill_ak2.
I believe I understand why the optimizer changes the plan: it thinks
that the larger time range will make the fill_ak2 index scan a more
expensive option than a sequential scan of the fill table. Can
someone recommend a way for me to show the optimizer the error of its
ways? (short of using 'set enable_seqscan = no', that is).
Incidentally, in our current SQL server database I use a hint to tell
the optimizer specifically what index to use. When using Oracle
databases in the past, I've had to do the same thing. I understand
the PostgreSQL developers are against this approach, preferring to
make the optimizer smarter instead. I agree with this in principal,
however, it seems to me that until the optimizer is perfect, this type
of feature is needed. Comments?
-K
^ permalink raw reply [nested|flat] 6+ messages in thread
* optimizer tuning/forcing correct index use
@ 2002-03-19 13:22 Kelly Burkhart <kelly@tradebotsystems.com>
2002-03-19 15:28 ` Re: optimizer tuning/forcing correct index use Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Kelly Burkhart @ 2002-03-19 13:22 UTC (permalink / raw)
To: pgsql-sql
Greetings,
I have a query that sometimes refuses to use a sensible index.
Some info first:
Version: PostgreSQL v 7.2
OS: SuSE Linux 7.2 w/Kernel 2.4.14
HW: Dual Athlon MP1800, 768MB RAM
DB exists on a single fast SCSI drive.
DB was loaded with a copy of our production system data (currently
running on a SQLServer database). 'vacuumdb -a -z' was run after
load, no rows have been added or deleted since vacuum.
Here is the query:
explain analyze
select
t.tb_order_number, f.account_number, f.clearing_account_id,
f.symbol_full_name, f.fill_quantity, f.buy_or_sell,
f.trader_username, f.fill_price, f.fill_quantity, f.fill_ts,
f.last_update_ts, s.symbol_type, s.base_symbol_full_name,
s.symbol_name, ca.clearing_account_number, ca.clearing_firm,
co.tick_value
from
fill f,
tb_order t,
symbol s,
clearing_account ca,
contract co
where
f.fill_ts > '2002-02-06 00:00:00' and
f.fill_ts <= '2002-02-08 23:59:59' and
t.order_id = f.order_id and
s.full_name = f.symbol_full_name and
co.contract_name = s.contract_name and
ca.clearing_account_id = f.clearing_account_id and
s.symbol_name not in ('ISLD:TESTA', 'ISLD:TESTB', 'ISLD:TESTC');
Total number of rows in the tables of the join:
fill : 7674725
tb_order : 20059204
symbol : 559
clearing_account : 57
contract : 13
When the time range (defined by fill_ts) is under 48 hours, the
following execution plan is used:
Nested Loop (cost=35.98..477477.54 rows=37403 width=170) (actual time=6.38..7868.65 rows=143582 loops=1)
-> Hash Join (cost=35.98..256958.14 rows=37403 width=152) (actual time=6.21..3937.04 rows=143582 loops=1)
-> Hash Join (cost=1.71..256229.53 rows=45364 width=90) (actual time=0.50..2685.68 rows=143582 loops=1)
-> Index Scan using fill_ak2 on fill f (cost=0.00..255314.58 rows=69239 width=66) (actual time=0.26..1875.38 rows=143582 loops=1)
-> Hash (cost=1.57..1.57 rows=57 width=24) (actual time=0.20..0.20 rows=0 loops=1)
-> Seq Scan on clearing_account ca (cost=0.00..1.57 rows=57 width=24) (actual time=0.05..0.14 rows=57 loops=1)
-> Hash (cost=32.89..32.89 rows=547 width=62) (actual time=5.66..5.66 rows=0 loops=1)
-> Hash Join (cost=1.16..32.89 rows=547 width=62) (actual time=0.21..4.73 rows=559 loops=1)
-> Seq Scan on symbol s (cost=0.00..20.78 rows=547 width=48) (actual time=0.05..2.43 rows=559 loops=1)
-> Hash (cost=1.13..1.13 rows=13 width=14) (actual time=0.11..0.11 rows=0 loops=1)
-> Seq Scan on contract co (cost=0.00..1.13 rows=13 width=14) (actual time=0.06..0.08 rows=13 loops=1)
-> Index Scan using order_pk on tb_order t (cost=0.00..5.88 rows=1 width=18) (actual time=0.02..0.02 rows=1 loops=143582)
Total runtime: 7934.64 msec
Somewhere above 48 hours the plan changes to this:
Nested Loop (cost=35.98..644155.97 rows=56104 width=170) (actual time=48504.60..128512.95 rows=196483 loops=1)
-> Hash Join (cost=35.98..313376.24 rows=56104 width=152) (actual time=48450.25..121546.48 rows=196483 loops=1)
-> Hash Join (cost=1.71..312300.45 rows=68045 width=90) (actual time=48444.62..119822.68 rows=196483 loops=1)
-> Seq Scan on fill f (cost=0.00..310928.88 rows=103859 width=66) (actual time=48444.38..118737.56 rows=196483 loops=1)
-> Hash (cost=1.57..1.57 rows=57 width=24) (actual time=0.19..0.19 rows=0 loops=1)
-> Seq Scan on clearing_account ca (cost=0.00..1.57 rows=57 width=24) (actual time=0.05..0.13 rows=57 loops=1)
-> Hash (cost=32.89..32.89 rows=547 width=62) (actual time=5.57..5.57 rows=0 loops=1)
-> Hash Join (cost=1.16..32.89 rows=547 width=62) (actual time=0.21..4.64 rows=559 loops=1)
-> Seq Scan on symbol s (cost=0.00..20.78 rows=547 width=48) (actual time=0.05..2.34 rows=559 loops=1)
-> Hash (cost=1.13..1.13 rows=13 width=14) (actual time=0.11..0.11 rows=0 loops=1)
-> Seq Scan on contract co (cost=0.00..1.13 rows=13 width=14) (actual time=0.07..0.09 rows=13 loops=1)
-> Index Scan using order_pk on tb_order t (cost=0.00..5.88 rows=1 width=18) (actual time=0.03..0.03 rows=1 loops=196483)
Total runtime: 128598.23 msec
The critical part is driving the fill table query from a sequential scan
rather than a scan of fill_ak2.
I believe I understand why the optimizer changes the plan: it thinks
that the larger time range will make the fill_ak2 index scan a more
expensive option than a sequential scan of the fill table. Can
someone recommend a way for me to show the optimizer the error of its
ways? (short of using 'set enable_seqscan = no', that is).
Incidentally, in our current SQL server database I use a hint to tell
the optimizer specifically what index to use. When using Oracle
databases in the past, I've had to do the same thing. I understand
the PostgreSQL developers are against this approach, preferring to
make the optimizer smarter instead. I agree with this in principal,
however, it seems to me that until the optimizer is perfect, this type
of feature is needed. Comments?
-K
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: optimizer tuning/forcing correct index use
2002-03-19 13:22 optimizer tuning/forcing correct index use Kelly Burkhart <kelly@tradebotsystems.com>
@ 2002-03-19 15:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-03-19 17:12 ` Re: optimizer tuning/forcing correct index use Kelly Burkhart <kelly@tradebotsystems.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2002-03-19 15:28 UTC (permalink / raw)
To: Kelly Burkhart <kelly@tradebotsystems.com>; +Cc: pgsql-sql
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.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: optimizer tuning/forcing correct index use
2002-03-19 13:22 optimizer tuning/forcing correct index use Kelly Burkhart <kelly@tradebotsystems.com>
2002-03-19 15:28 ` Re: optimizer tuning/forcing correct index use Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-03-19 17:12 ` Kelly Burkhart <kelly@tradebotsystems.com>
2002-03-19 17:32 ` Re: optimizer tuning/forcing correct index use Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Kelly Burkhart @ 2002-03-19 17:12 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: optimizer tuning/forcing correct index use
2002-03-19 13:22 optimizer tuning/forcing correct index use Kelly Burkhart <kelly@tradebotsystems.com>
2002-03-19 15:28 ` Re: optimizer tuning/forcing correct index use Tom Lane <tgl@sss.pgh.pa.us>
2002-03-19 17:12 ` Re: optimizer tuning/forcing correct index use Kelly Burkhart <kelly@tradebotsystems.com>
@ 2002-03-19 17:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-03-19 18:51 ` Re: optimizer tuning/forcing correct index use Kelly Burkhart <kelly@tradebotsystems.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2002-03-19 17:32 UTC (permalink / raw)
To: Kelly Burkhart <kelly@tradebotsystems.com>; +Cc: pgsql-sql
Kelly Burkhart <kelly@tradebotsystems.com> writes:
>> 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.
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
Hmm. So the correlation of fill_ts with physical position is actually
negative, according to the analyze results. Still, -0.54 represents
rather strong correlation which would reduce the cost of the index scan.
There was some discussion a couple weeks ago on the pgsql-bugs list about
changing the equation the planner uses to estimate the effects of
correlation order. Are you interested in experimenting? I previously
said:
: If you look in cost_index (see approx. lines 270-340 in
: src/backend/optimizer/path/costsize.c) you'll see that it computes
: access cost estimates for both the perfectly sequential case and
: the perfectly uncorrelated case, and then tries to interpolate
: between them. I have reasonable faith in both of the endpoint
: estimation methods, but very little in the interpolation equation ---
: it was chosen on the spur of the moment and hasn't really been tested.
:
: It might be interesting to replace csquared with just
: fabs(indexCorrelation) to see if the results are better.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: optimizer tuning/forcing correct index use
2002-03-19 13:22 optimizer tuning/forcing correct index use Kelly Burkhart <kelly@tradebotsystems.com>
2002-03-19 15:28 ` Re: optimizer tuning/forcing correct index use Tom Lane <tgl@sss.pgh.pa.us>
2002-03-19 17:12 ` Re: optimizer tuning/forcing correct index use Kelly Burkhart <kelly@tradebotsystems.com>
2002-03-19 17:32 ` Re: optimizer tuning/forcing correct index use Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-03-19 18:51 ` Kelly Burkhart <kelly@tradebotsystems.com>
0 siblings, 0 replies; 6+ messages in thread
From: Kelly Burkhart @ 2002-03-19 18:51 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
On Tuesday 19 March 2002 11:32 am, Tom Lane wrote:
<snip>
>
> Hmm. So the correlation of fill_ts with physical position is actually
> negative, according to the analyze results. Still, -0.54 represents
> rather strong correlation which would reduce the cost of the index scan.
>
> There was some discussion a couple weeks ago on the pgsql-bugs list about
> changing the equation the planner uses to estimate the effects of
> correlation order. Are you interested in experimenting? I previously
>
> said:
> : If you look in cost_index (see approx. lines 270-340 in
> : src/backend/optimizer/path/costsize.c) you'll see that it computes
> : access cost estimates for both the perfectly sequential case and
> : the perfectly uncorrelated case, and then tries to interpolate
> : between them. I have reasonable faith in both of the endpoint
> : estimation methods, but very little in the interpolation equation ---
> : it was chosen on the spur of the moment and hasn't really been tested.
> :
> : It might be interesting to replace csquared with just
> : fabs(indexCorrelation) to see if the results are better.
in costsize.c:334 I've changed:
csquared = indexCorrelation * indexCorrelation;
to:
csquared = fabs(indexCorrelation);
It looks like the estimated cost of the fill index scan is lower, but not
enough to change the plan.
I'll be happy to run more tests if you can think of other things to try.
-K
Attachments:
test_results.txt (text/plain, 10.6K) download
explain analyze
select
t.tb_order_number, f.account_number, f.clearing_account_id,
f.symbol_full_name, f.fill_quantity, f.buy_or_sell,
f.trader_username, f.fill_price, f.fill_quantity, f.fill_ts,
f.last_update_ts, s.symbol_type, s.base_symbol_full_name,
s.symbol_name, ca.clearing_account_number, ca.clearing_firm,
co.tick_value
from
fill f,
tb_order t,
symbol s,
clearing_account ca,
contract co
where
f.fill_ts > '2002-02-04 00:00:00' and
f.fill_ts <= '2002-02-06 23:59:59' and
t.order_id = f.order_id and
s.full_name = f.symbol_full_name and
co.contract_name = s.contract_name and
ca.clearing_account_id = f.clearing_account_id and
s.symbol_name not in ('ISLD:TESTA', 'ISLD:TESTB', 'ISLD:TESTC');
-----------------------------------------------------------------------------
using csquared = fabs(indexCorrelation);
f.fill_ts > '2002-02-04 00:00:00' and
f.fill_ts <= '2002-02-04 23:59:59' and
Hash Join (cost=35.98..250269.85 rows=25830 width=170) (actual time=251.27..4578.73 rows=48633 loops=1)
-> Nested Loop (cost=34.26..249816.11 rows=25830 width=146) (actual time=235.52..4193.11 rows=48633 loops=1)
-> Hash Join (cost=34.26..107159.54 rows=25830 width=128) (actual time=141.91..2035.45 rows=48633 loops=1)
-> Index Scan using fill_ak2 on fill f (cost=0.00..106619.81 rows=36518 width=66) (actual time=125.70..1645.10 rows=48633 loops=1)
-> Hash (cost=32.89..32.89 rows=547 width=62) (actual time=16.16..16.16 rows=0 loops=1)
-> Hash Join (cost=1.16..32.89 rows=547 width=62) (actual time=10.79..15.26 rows=559 loops=1)
-> Seq Scan on symbol s (cost=0.00..20.78 rows=547 width=48) (actual time=0.07..2.40 rows=559 loops=1)
-> Hash (cost=1.13..1.13 rows=13 width=14) (actual time=10.67..10.67 rows=0 loops=1)
-> Seq Scan on contract co (cost=0.00..1.13 rows=13 width=14) (actual time=10.62..10.64 rows=13 loops=1)
-> Index Scan using order_pk on tb_order t (cost=0.00..5.51 rows=1 width=18) (actual time=0.03..0.04 rows=1 loops=48633)
-> Hash (cost=1.57..1.57 rows=57 width=24) (actual time=15.68..15.68 rows=0 loops=1)
-> Seq Scan on clearing_account ca (cost=0.00..1.57 rows=57 width=24) (actual time=15.52..15.61 rows=57 loops=1)
Total runtime: 4599.75 msec
-----------------------------------------------------------------------------
using csquared = fabs(indexCorrelation);
f.fill_ts > '2002-02-04 00:00:00' and
f.fill_ts <= '2002-02-05 23:59:59' and
Hash Join (cost=35.98..495564.76 rows=51152 width=170) (actual time=6.41..6418.32 rows=113026 loops=1)
-> Nested Loop (cost=34.26..494667.89 rows=51152 width=146) (actual time=6.13..5555.21 rows=113026 loops=1)
-> Hash Join (cost=34.26..212165.66 rows=51152 width=128) (actual time=5.99..2435.00 rows=113026 loops=1)
-> Index Scan using fill_ak2 on fill f (cost=0.00..211130.42 rows=72316 width=66) (actual time=0.27..1559.79 rows=113026 loops=1)
-> Hash (cost=32.89..32.89 rows=547 width=62) (actual time=5.67..5.67 rows=0 loops=1)
-> Hash Join (cost=1.16..32.89 rows=547 width=62) (actual time=0.21..4.72 rows=559 loops=1)
-> Seq Scan on symbol s (cost=0.00..20.78 rows=547 width=48) (actual time=0.05..2.37 rows=559 loops=1)
-> Hash (cost=1.13..1.13 rows=13 width=14) (actual time=0.10..0.10 rows=0 loops=1)
-> Seq Scan on contract co (cost=0.00..1.13 rows=13 width=14) (actual time=0.05..0.07 rows=13 loops=1)
-> Index Scan using order_pk on tb_order t (cost=0.00..5.51 rows=1 width=18) (actual time=0.02..0.02 rows=1 loops=113026)
-> Hash (cost=1.57..1.57 rows=57 width=24) (actual time=0.23..0.23 rows=0 loops=1)
-> Seq Scan on clearing_account ca (cost=0.00..1.57 rows=57 width=24) (actual time=0.07..0.16 rows=57 loops=1)
Total runtime: 6465.18 msec
-----------------------------------------------------------------------------
using csquared = fabs(indexCorrelation);
f.fill_ts > '2002-02-04 00:00:00' and
f.fill_ts <= '2002-02-06 23:59:59' and
Nested Loop (cost=35.98..739742.66 rows=76473 width=170) (actual time=49118.56..130890.07 rows=191483 loops=1)
-> Hash Join (cost=35.98..317394.77 rows=76473 width=152) (actual time=49067.80..125663.26 rows=191483 loops=1)
-> Hash Join (cost=34.26..316054.79 rows=76473 width=128) (actual time=49067.51..124002.64 rows=191483 loops=1)
-> Seq Scan on fill f (cost=0.00..314524.04 rows=108115 width=66) (actual time=49061.88..122556.03 rows=191483 loops=1)
-> Hash (cost=32.89..32.89 rows=547 width=62) (actual time=5.57..5.57 rows=0 loops=1)
-> Hash Join (cost=1.16..32.89 rows=547 width=62) (actual time=0.19..4.67 rows=559 loops=1)
-> Seq Scan on symbol s (cost=0.00..20.78 rows=547 width=48) (actual time=0.05..2.37 rows=559 loops=1)
-> Hash (cost=1.13..1.13 rows=13 width=14) (actual time=0.09..0.09 rows=0 loops=1)
-> Seq Scan on contract co (cost=0.00..1.13 rows=13 width=14) (actual time=0.04..0.06 rows=13 loops=1)
-> Hash (cost=1.57..1.57 rows=57 width=24) (actual time=0.23..0.23 rows=0 loops=1)
-> Seq Scan on clearing_account ca (cost=0.00..1.57 rows=57 width=24) (actual time=0.06..0.15 rows=57 loops=1)
-> Index Scan using order_pk on tb_order t (cost=0.00..5.51 rows=1 width=18) (actual time=0.02..0.02 rows=1 loops=191483)
Total runtime: 130973.82 msec
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
csquared = indexCorrelation * indexCorrelation;
f.fill_ts > '2002-02-04 00:00:00' and
f.fill_ts <= '2002-02-04 23:59:59' and
Nested Loop (cost=35.98..289773.54 rows=25830 width=170) (actual time=184.26..2418.14 rows=48633 loops=1)
-> Hash Join (cost=35.98..136300.26 rows=25830 width=152) (actual time=139.78..1340.42 rows=48633 loops=1)
-> Hash Join (cost=34.26..135846.52 rows=25830 width=128) (actual time=124.06..901.60 rows=48633 loops=1)
-> Index Scan using fill_ak2 on fill f (cost=0.00..135306.79 rows=36518 width=66) (actual time=107.86..509.52 rows=48633 loops=1)
-> Hash (cost=32.89..32.89 rows=547 width=62) (actual time=16.13..16.13 rows=0 loops=1)
-> Hash Join (cost=1.16..32.89 rows=547 width=62) (actual time=10.79..15.23 rows=559 loops=1)
-> Seq Scan on symbol s (cost=0.00..20.78 rows=547 width=48) (actual time=0.07..2.40 rows=559 loops=1)
-> Hash (cost=1.13..1.13 rows=13 width=14) (actual time=10.67..10.67 rows=0 loops=1)
-> Seq Scan on contract co (cost=0.00..1.13 rows=13 width=14) (actual time=10.63..10.64 rows=13 loops=1)
-> Hash (cost=1.57..1.57 rows=57 width=24) (actual time=15.67..15.67 rows=0 loops=1)
-> Seq Scan on clearing_account ca (cost=0.00..1.57 rows=57 width=24) (actual time=15.50..15.59 rows=57 loops=1)
-> Index Scan using order_pk on tb_order t (cost=0.00..5.93 rows=1 width=18) (actual time=0.01..0.01 rows=1 loops=48633)
Total runtime: 2439.64 msec
-----------------------------------------------------------------------------
csquared = indexCorrelation * indexCorrelation;
f.fill_ts > '2002-02-04 00:00:00' and
f.fill_ts <= '2002-02-05 23:59:59' and
Nested Loop (cost=35.98..573792.17 rows=51152 width=170) (actual time=6.23..6919.29 rows=113026 loops=1)
-> Hash Join (cost=35.98..269869.65 rows=51152 width=152) (actual time=6.09..3712.27 rows=113026 loops=1)
-> Hash Join (cost=34.26..268972.79 rows=51152 width=128) (actual time=5.81..2727.98 rows=113026 loops=1)
-> Index Scan using fill_ak2 on fill f (cost=0.00..267937.55 rows=72316 width=66) (actual time=0.27..1848.67 rows=113026 loops=1)
-> Hash (cost=32.89..32.89 rows=547 width=62) (actual time=5.49..5.49 rows=0 loops=1)
-> Hash Join (cost=1.16..32.89 rows=547 width=62) (actual time=0.19..4.59 rows=559 loops=1)
-> Seq Scan on symbol s (cost=0.00..20.78 rows=547 width=48) (actual time=0.06..2.35 rows=559 loops=1)
-> Hash (cost=1.13..1.13 rows=13 width=14) (actual time=0.09..0.09 rows=0 loops=1)
-> Seq Scan on contract co (cost=0.00..1.13 rows=13 width=14) (actual time=0.04..0.06 rows=13 loops=1)
-> Hash (cost=1.57..1.57 rows=57 width=24) (actual time=0.23..0.23 rows=0 loops=1)
-> Seq Scan on clearing_account ca (cost=0.00..1.57 rows=57 width=24) (actual time=0.07..0.16 rows=57 loops=1)
-> Index Scan using order_pk on tb_order t (cost=0.00..5.93 rows=1 width=18) (actual time=0.02..0.02 rows=1 loops=113026)
Total runtime: 6967.91 msec
-----------------------------------------------------------------------------
csquared = indexCorrelation * indexCorrelation;
f.fill_ts > '2002-02-04 00:00:00' and
f.fill_ts <= '2002-02-06 23:59:59' and
Nested Loop (cost=35.98..771766.53 rows=76473 width=170) (actual time=49185.87..131460.79 rows=191483 loops=1)
-> Hash Join (cost=35.98..317394.77 rows=76473 width=152) (actual time=49135.04..126128.47 rows=191483 loops=1)
-> Hash Join (cost=34.26..316054.79 rows=76473 width=128) (actual time=49134.75..124465.11 rows=191483 loops=1)
-> Seq Scan on fill f (cost=0.00..314524.04 rows=108115 width=66) (actual time=49129.08..123019.27 rows=191483 loops=1)
-> Hash (cost=32.89..32.89 rows=547 width=62) (actual time=5.60..5.60 rows=0 loops=1)
-> Hash Join (cost=1.16..32.89 rows=547 width=62) (actual time=0.19..4.68 rows=559 loops=1)
-> Seq Scan on symbol s (cost=0.00..20.78 rows=547 width=48) (actual time=0.05..2.38 rows=559 loops=1)
-> Hash (cost=1.13..1.13 rows=13 width=14) (actual time=0.09..0.09 rows=0 loops=1)
-> Seq Scan on contract co (cost=0.00..1.13 rows=13 width=14) (actual time=0.04..0.06 rows=13 loops=1)
-> Hash (cost=1.57..1.57 rows=57 width=24) (actual time=0.23..0.23 rows=0 loops=1)
-> Seq Scan on clearing_account ca (cost=0.00..1.57 rows=57 width=24) (actual time=0.07..0.15 rows=57 loops=1)
-> Index Scan using order_pk on tb_order t (cost=0.00..5.93 rows=1 width=18) (actual time=0.02..0.02 rows=1 loops=191483)
Total runtime: 131542.17 msec
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2002-03-19 18:51 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-03-15 16:03 optimizer tuning/forcing correct index use Kelly Burkhart <kelly@tradebotsystems.com>
2002-03-19 13:22 optimizer tuning/forcing correct index use Kelly Burkhart <kelly@tradebotsystems.com>
2002-03-19 15:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-03-19 17:12 ` Kelly Burkhart <kelly@tradebotsystems.com>
2002-03-19 17:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-03-19 18:51 ` Kelly Burkhart <kelly@tradebotsystems.com>
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