All,
One of the Sun benchmarking guys ran across this issue, using a fairly
recent (within the last 4 weeks) 8.3 snapshot. It appears to me that the
8.3 planner is unable to tell that, if the value for the first column of
an index scan is a constant, the second column doesn't need to be sorted.
This was working in 8.2.
Am I interpreting this wrong? All data was ANALYZED.
==
8.3 plan
postgres @ bigleaf% psql tpce -c '\i tl3_1.sql'
QUERY
PLAN
-
Limit (cost=988.46..988.47 rows=20 width=64) (actual time=9.444..9.457
rows=20 loops=1)
- Sort (cost=988.46..988.62 rows=647 width=64) (actual
time=9.440..9.443 rows=20 loops=1)
Sort Key: t_dts
- Index Scan using idx_t_s_symb_dts on trade
(cost=0.00..985.44 rows=647 width=64) (actual time=0.166..6.629 rows=845
loops=1)
Index Cond: (((t_s_symb)::text = 'SYMPRA'::text) AND
(t_dts = '2006-02-15 00:00:00'::timestamp without time zone) AND (t_dts
= '2007-06-28 00:00:00'::timestamp without time zone))
Filter: ((t_ca_id = 435::bigint) AND (t_st_id =
'CMPT'::bpchar))
Total runtime: 9.679 ms
(7 rows)
8.2.1 plan...
postgres @ bigleaf% psql tpce -c '\i tl3_1.sql'
QUERY
PLAN
---
Limit (cost=0.00..30.48 rows=20 width=64) (actual time=0.217..0.343
rows=20 loops=1)
- Index Scan using idx_t_s_symb_dts on trade (cost=0.00..944.86
rows=620 width=64) (actual time=0.212..0.332 rows=20 loops=1)
Index Cond: (((t_s_symb)::text = 'SYMPRA'::text) AND (t_dts =
'2006-02-15 00:00:00'::timestamp without time zone) AND (t_dts =
'2007-06-28 00:00:00'::timestamp without time zone))
Filter: ((t_st_id = 'CMPT'::bpchar) AND (t_ca_id =
435::bigint))
Total runtime: 0.644 ms
(5 rows)
EXPLAIN ANALYZE
SELECT t_id, t_ca_id, t_exec_name, t_trade_price,
t_qty, t_dts, t_tt_id, t_is_cash
FROM trade
WHERE t_s_symb = 'SYMPRA'
AND t_st_id = 'CMPT'
AND t_dts = '2006-02-15'
AND t_dts = '2007-06-28'
AND t_ca_id = 435 -- This test is not required
for a reportable run
ORDER BY t_dts ASC
LIMIT 20;
tpce=# \d+ trade
Table public.trade
Column | Type | Modifiers | Description
---+-+---+-
t_id | bigint | not null |
t_dts | timestamp without time zone | not null |
t_st_id | character(4) | not null |
t_tt_id | character(3) | not null |
t_is_cash | boolean | not null |
t_s_symb | character varying(15) | not null |
t_qty | integer | not null |
t_bid_price | numeric(8,2) | not null |
t_ca_id | bigint | not null |
t_exec_name | character varying(64) | not null |
t_trade_price | numeric(8,2) | |
t_chrg | numeric(10,2) | |
t_comm | numeric(10,2) | not null |
t_tax | numeric(10,2) | not null |
t_lifo | boolean | not null |
Indexes:
trade_pkey PRIMARY KEY, btree (t_id)
idx_t_ca_id_dts btree (t_ca_id, t_dts)
idx_t_s_symb_dts btree (t_s_symb, t_dts)
Check constraints:
trade_t_bid_price_check CHECK (t_bid_price 0::numeric)
trade_t_chrg_check CHECK (t_chrg = 0::numeric)
trade_t_comm_check CHECK (t_comm = 0::numeric)
trade_t_qty_check CHECK (t_qty 0)
trade_t_tax_check CHECK (t_tax = 0::numeric)
Has OIDs: no
--
--Josh
Josh Berkus
PostgreSQL @ Sun
San Francisco
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
