Incorrect Sort Using Index Scan - Mailing list pgsql-bugs
From | McGehee, Robert |
---|---|
Subject | Incorrect Sort Using Index Scan |
Date | |
Msg-id | 17B09E7789D3104E8F5EEB0582A8D66FF24242683B@MSGRTPCCRF2WIN.DMN1.FMR.COM Whole thread Raw |
Responses |
Re: Incorrect Sort Using Index Scan
(Tom Lane <tgl@sss.pgh.pa.us>)
|
List | pgsql-bugs |
Hello, I have recently upgraded to PostgreSQL 9.2.0 and one of my queries is now g= iving incorrectly sorted results due, I believe, to the new index-only scan= in 9.2.0. The below table is a table of currency FX rates by date. template1=3D# \d fx0; Table "public.fx0" Column | Type | Modifiers ---------+----------------------+----------- date | date | not null fromcur | character varying(3) | not null fx | numeric(16,8) | not null tocur | character varying(3) | not null Indexes: "pk_fx0" PRIMARY KEY, btree (date, fromcur, tocur) This query correctly sorts the chosen two currencies by date; > SELECT * FROM fx0 WHERE fromcur IN ('AUD','JPY') ORDER BY date desc; date | fromcur | fx | tocur ------------+---------+------------+------- 2012-09-14 | JPY | 0.01276592 | USD 2012-09-14 | AUD | 1.05741440 | USD 2012-09-13 | JPY | 0.01291478 | USD 2012-09-13 | AUD | 1.04486224 | USD 2012-09-12 | AUD | 1.04491173 | USD 2012-09-12 | JPY | 0.01284250 | USD 2012-09-11 | JPY | 0.01285457 | USD 2012-09-11 | AUD | 1.04324956 | USD <...snip...> template1=3D# explain select * FROM fx0 where fromcur IN ('AUD','JPY') ORDE= R BY date desc; QUERY PLAN ------------------------------------------------------------------ Sort (cost=3D21139.98..21181.31 rows=3D16530 width=3D19) Sort Key: date -> Seq Scan on fx0 (cost=3D0.00..19981.83 rows=3D16530 width=3D19) Filter: ((fromcur)::text =3D ANY ('{AUD,JPY}'::text[])) (4 rows) Now I will add a simple condition that date >=3D'2012-09-11'. The output sh= ould exactly match the (truncated) results above: template1=3D# select * FROM fx0 where fromcur IN ('AUD','JPY') and date >= =3D'2012-09-11' ORDER BY date desc; date | fromcur | fx | tocur ------------+---------+------------+------- 2012-09-14 | JPY | 0.01276592 | USD 2012-09-13 | JPY | 0.01291478 | USD 2012-09-12 | JPY | 0.01284250 | USD 2012-09-11 | JPY | 0.01285457 | USD 2012-09-14 | AUD | 1.05741440 | USD 2012-09-13 | AUD | 1.04486224 | USD 2012-09-12 | AUD | 1.04491173 | USD 2012-09-11 | AUD | 1.04324956 | USD (8 rows) Note however that the table was NOT sorted by date, but by fromcur (descend= ing), then date. Let's look at the explain: template1=3D# explain select * FROM fx0 where fromcur IN ('AUD','JPY') and = date >=3D'2012-09-11' ORDER BY date desc; QUERY PLAN ---------------------------------------------------------------------------= --------------------- Index Scan Backward using pk_fx0 on fx0 (cost=3D0.00..40.54 rows=3D12 wid= th=3D19) Index Cond: ((date >=3D '2012-09-11'::date) AND ((fromcur)::text =3D ANY= ('{AUD,JPY}'::text[]))) (2 rows) The first (correct) query used a Seq Scan, and the second (incorrect) a Ind= ex Scan, leading me to believe there's a problem with the Index Scan in thi= s query and/or table design. Please let me know if I can provide any other information. Regards, Robert McGehee PS. I analyzed, vacuumed, reindexed, clustered this table, and even made a = a brand new copy, but the problem persists.
pgsql-bugs by date: