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 12:51:34 -0600
Message-ID: <0203191226350A.00735@krbdev> (raw )
In-Reply-To: <160.1016559143@sss.pgh.pa.us >
References: <02031907224907.00735@krbdev >
<02031911121009.00735@krbdev >
<160.1016559143@sss.pgh.pa.us >
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
view thread (6+ messages)
Message-ID: <0203191226350A.00735@krbdev>
Permalink: ../0203191226350A.00735@krbdev/
Also on: postgresql.org/message-id/0203191226350A.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: <0203191226350A.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