Incorrect Sort Using Index Scan

Поиск
Список
Период
Сортировка
От McGehee, Robert
Тема Incorrect Sort Using Index Scan
Дата
Msg-id 17B09E7789D3104E8F5EEB0582A8D66FF24242683B@MSGRTPCCRF2WIN.DMN1.FMR.COM
обсуждение исходный текст
Ответы Re: Incorrect Sort Using Index Scan  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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 по дате отправления:

Предыдущее
От: andrei@tchijov.com
Дата:
Сообщение: BUG #7552: where clause gets ignored on one of view fields
Следующее
От: Tom Lane
Дата:
Сообщение: Re: BUG #7552: where clause gets ignored on one of view fields