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

 · 

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