possible de-optimization of multi-column index plans in 8.3 - Mailing list pgsql-hackers

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