pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedIncorrect Sort Using Index Scan
2+ messages / 2 participants
[nested] [flat]
* Incorrect Sort Using Index Scan
@ 2012-09-17 21:11 McGehee, Robert <Robert.McGehee@geodecapital.com>
0 siblings, 1 reply; 2+ messages in thread
From: McGehee, Robert @ 2012-09-17 21:11 UTC (permalink / raw)
To: pgsql-bugs
Hello,
I have recently upgraded to PostgreSQL 9.2.0 and one of my queries is now giving 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=# \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=# explain select * FROM fx0 where fromcur IN ('AUD','JPY') ORDER BY date desc;
QUERY PLAN
------------------------------------------------------------------
Sort (cost=21139.98..21181.31 rows=16530 width=19)
Sort Key: date
-> Seq Scan on fx0 (cost=0.00..19981.83 rows=16530 width=19)
Filter: ((fromcur)::text = ANY ('{AUD,JPY}'::text[]))
(4 rows)
Now I will add a simple condition that date >='2012-09-11'. The output should exactly match the (truncated) results above:
template1=# select * FROM fx0 where fromcur IN ('AUD','JPY') and date >='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 (descending), then date. Let's look at the explain:
template1=# explain select * FROM fx0 where fromcur IN ('AUD','JPY') and date >='2012-09-11' ORDER BY date desc;
QUERY PLAN
------------------------------------------------------------------------------------------------
Index Scan Backward using pk_fx0 on fx0 (cost=0.00..40.54 rows=12 width=19)
Index Cond: ((date >= '2012-09-11'::date) AND ((fromcur)::text = ANY ('{AUD,JPY}'::text[])))
(2 rows)
The first (correct) query used a Seq Scan, and the second (incorrect) a Index Scan, leading me to believe there's a problem with the Index Scan in this 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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Incorrect Sort Using Index Scan
@ 2012-09-18 05:17 Tom Lane <tgl@sss.pgh.pa.us>
parent: McGehee, Robert <Robert.McGehee@geodecapital.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2012-09-18 05:17 UTC (permalink / raw)
To: McGehee, Robert <Robert.McGehee@geodecapital.com>; +Cc: pgsql-bugs
"McGehee, Robert" <Robert.McGehee@geodecapital.com> writes:
> I have recently upgraded to PostgreSQL 9.2.0 and one of my queries is now giving 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.
Hmm, no it's not about index-only scans (your query isn't even using
one). I think this got broken in my commit
9e8da0f75731aaa7605cf4656c21ea09e84d2eb1, which turned =ANY into a
native index qualification type for btree. I thought that the output
could still be considered sorted by the index's sort order, but this
example proves that that's not so. Need to think a bit more carefully
about that --- maybe we can only assume sorted output when the =ANY qual
is for the first index column. Or maybe we can't assume it at all.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2012-09-18 05:17 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-09-17 21:11 Incorrect Sort Using Index Scan McGehee, Robert <Robert.McGehee@geodecapital.com>
2012-09-18 05:17 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox