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 <= 43000050000::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 <= 43000050000::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 <= 43000050000 -- 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
