pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedorder by and limit with multi-column index, bug?
3+ messages / 2 participants
[nested] [flat]
* order by and limit with multi-column index, bug?
@ 2002-12-13 14:34 Harald Krake <harald@krake.de>
2002-12-13 14:47 ` Re: order by and limit with multi-column index, bug? Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz>
0 siblings, 1 reply; 3+ messages in thread
From: Harald Krake @ 2002-12-13 14:34 UTC (permalink / raw)
To: pgsql-sql
as a workaround for min()/max() on indexed columns forcing an index-scan
I tried "order by" with "limit 1". Works fine for the first record
but fails for the last. I don't know why.
Here's the setup:
A table "journal" containing several million records with an index
"CREATE INDEX journal_kblattsoll ON journal
(sollkontoid,waehrungid,periode,belegdatum,journalnr)"
with periode being an INT, belegdatum DATE, the rest is INT8.
As a replacement for
"select min(periode) from journal
where sollkontoid=266122::int8 and waehrungid=17::int8"
which for some reason in the design of postgres scans the whole index,
I tried:
"select sollkontoid,waehrungid,periode,belegdatum,journalnr from journal
where sollkontoid=266122::int8 and waehrungid=17::int8
order by sollkontoid,waehrungid,periode,belegdatum,journalnr asc limit 1;"
this yields:
sollkontoid | waehrungid | periode | belegdatum | journalnr
-------------+------------+---------+------------+-----------
266122 | 17 | 0 | 2002-01-01 | 411
which is correct and works in a fraction of a second as expected.
now, doing the same with "desc" instead of "asc" should return
"periode = 12" (see below) for the last record, but it doesn't!
After a fairly long time I get:
sollkontoid | waehrungid | periode | belegdatum | journalnr
-------------+------------+---------+------------+-----------
266122 | 17 | 0 | 2002-01-01 | 2783
ooops???! periode = 0???
Query plan:
Limit (cost=491999.72..491999.73 rows=1 width=32)
-> Sort (cost=491999.72..492309.30 rows=123828 width=32)
Sort Key: sollkontoid, waehrungid, periode, belegdatum, journalnr
-> Index Scan using journal_kblattsoll on journal
(cost=0.00..481525.10 rows=123828 width=32)
Index Cond: ((sollkontoid = 266122::bigint) AND (waehrungid =
17::bigint))
Surprisingly enough, reducing the fields in the order-by clause
returns the correct value for "periode":
select sollkontoid,waehrungid,periode,belegdatum,journalnr from journal
where sollkontoid=266122::int8 and waehrungid=17::int8
order by sollkontoid,waehrungid,periode desc limit 1;
sollkontoid | waehrungid | periode | belegdatum | journalnr
-------------+------------+---------+------------+-----------
266122 | 17 | 12 | 2002-12-09 | 303609
min/max-checks:
select max(periode)from journal where sollkontoid=266122::int8 and
waehrungid=17::int8;
returns 12.
select max(belegdatum) from journal where sollkontoid=266122::int8 and
waehrungid=17::int8 and periode=12;
returns "2002-12-10"
select max(journalnr) from journal where sollkontoid=266122::int8 and
waehrungid=17::int8 and periode=12 and belegdatum='2002-12-10';
returns 305098.
Consequently, the last record according to the order by clause should be:
sollkontoid | waehrungid | periode | belegdatum | journalnr
-------------+------------+---------+------------+-----------
266122 | 17 | 12 | 2002-12-10 | 305098
questions:
- what's this???
- why does it take that long?
- is "ORDER BY ... DESC" broken? (even after dropping the index I get
the same results)
- am I missing something (friday, 13th, ...) ?
thanx for any answer,
harald.
(postgres 7.3 on redhat 8.0)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: order by and limit with multi-column index, bug?
2002-12-13 14:34 order by and limit with multi-column index, bug? Harald Krake <harald@krake.de>
@ 2002-12-13 14:47 ` Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz>
2002-12-13 14:52 ` Re: order by and limit with multi-column index, bug? Harald Krake <harald@krake.de>
0 siblings, 1 reply; 3+ messages in thread
From: Jakub Ouhrabka @ 2002-12-13 14:47 UTC (permalink / raw)
To: Harald Krake <harald@krake.de>; +Cc: pgsql-sql
hi,
could it be that "order by col1, col2 desc" is different from "order by
col1 desc, col2 desc" ? these are different and it's correct.
i'm not sure if this is your problem since i haven't digged into it...
sorry... but check it to be sure...
hth,
kuba
On Fri, 13 Dec 2002, Harald Krake wrote:
> as a workaround for min()/max() on indexed columns forcing an index-scan
> I tried "order by" with "limit 1". Works fine for the first record
> but fails for the last. I don't know why.
>
> Here's the setup:
>
> A table "journal" containing several million records with an index
>
> "CREATE INDEX journal_kblattsoll ON journal
> (sollkontoid,waehrungid,periode,belegdatum,journalnr)"
>
> with periode being an INT, belegdatum DATE, the rest is INT8.
>
> As a replacement for
>
> "select min(periode) from journal
> where sollkontoid=266122::int8 and waehrungid=17::int8"
>
> which for some reason in the design of postgres scans the whole index,
> I tried:
>
> "select sollkontoid,waehrungid,periode,belegdatum,journalnr from journal
> where sollkontoid=266122::int8 and waehrungid=17::int8
> order by sollkontoid,waehrungid,periode,belegdatum,journalnr asc limit 1;"
>
> this yields:
> sollkontoid | waehrungid | periode | belegdatum | journalnr
> -------------+------------+---------+------------+-----------
> 266122 | 17 | 0 | 2002-01-01 | 411
>
> which is correct and works in a fraction of a second as expected.
>
> now, doing the same with "desc" instead of "asc" should return
> "periode = 12" (see below) for the last record, but it doesn't!
>
> After a fairly long time I get:
>
> sollkontoid | waehrungid | periode | belegdatum | journalnr
> -------------+------------+---------+------------+-----------
> 266122 | 17 | 0 | 2002-01-01 | 2783
>
> ooops???! periode = 0???
>
> Query plan:
> Limit (cost=491999.72..491999.73 rows=1 width=32)
> -> Sort (cost=491999.72..492309.30 rows=123828 width=32)
> Sort Key: sollkontoid, waehrungid, periode, belegdatum, journalnr
> -> Index Scan using journal_kblattsoll on journal
> (cost=0.00..481525.10 rows=123828 width=32)
> Index Cond: ((sollkontoid = 266122::bigint) AND (waehrungid =
> 17::bigint))
>
>
> Surprisingly enough, reducing the fields in the order-by clause
> returns the correct value for "periode":
>
> select sollkontoid,waehrungid,periode,belegdatum,journalnr from journal
> where sollkontoid=266122::int8 and waehrungid=17::int8
> order by sollkontoid,waehrungid,periode desc limit 1;
>
> sollkontoid | waehrungid | periode | belegdatum | journalnr
> -------------+------------+---------+------------+-----------
> 266122 | 17 | 12 | 2002-12-09 | 303609
>
>
>
> min/max-checks:
>
> select max(periode)from journal where sollkontoid=266122::int8 and
> waehrungid=17::int8;
>
> returns 12.
>
> select max(belegdatum) from journal where sollkontoid=266122::int8 and
> waehrungid=17::int8 and periode=12;
>
> returns "2002-12-10"
>
> select max(journalnr) from journal where sollkontoid=266122::int8 and
> waehrungid=17::int8 and periode=12 and belegdatum='2002-12-10';
>
> returns 305098.
>
> Consequently, the last record according to the order by clause should be:
>
> sollkontoid | waehrungid | periode | belegdatum | journalnr
> -------------+------------+---------+------------+-----------
> 266122 | 17 | 12 | 2002-12-10 | 305098
>
>
>
>
> questions:
>
> - what's this???
> - why does it take that long?
> - is "ORDER BY ... DESC" broken? (even after dropping the index I get
> the same results)
> - am I missing something (friday, 13th, ...) ?
>
> thanx for any answer,
> harald.
>
> (postgres 7.3 on redhat 8.0)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: order by and limit with multi-column index, bug?
2002-12-13 14:34 order by and limit with multi-column index, bug? Harald Krake <harald@krake.de>
2002-12-13 14:47 ` Re: order by and limit with multi-column index, bug? Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz>
@ 2002-12-13 14:52 ` Harald Krake <harald@krake.de>
0 siblings, 0 replies; 3+ messages in thread
From: Harald Krake @ 2002-12-13 14:52 UTC (permalink / raw)
To: Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz>; +Cc: pgsql-sql
On Friday 13 December 2002 03:47 pm, Jakub Ouhrabka wrote:
> hi,
>
> could it be that "order by col1, col2 desc" is different from "order by
> col1 desc, col2 desc" ? these are different and it's correct.
>
> i'm not sure if this is your problem since i haven't digged into it...
> sorry... but check it to be sure...
>
> hth,
>
> kuba
bingo!
good morning....
thanx a lot!
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-12-13 14:52 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-12-13 14:34 order by and limit with multi-column index, bug? Harald Krake <harald@krake.de>
2002-12-13 14:47 ` Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz>
2002-12-13 14:52 ` Harald Krake <harald@krake.de>
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