"Kevin Grittner" <kevin.gritt...@wicourts.gov> wrote: > Denis Kolesnik <lirex.softw...@gmail.com> wrote: >> and even sorting by id: >> select id, str_last_name from tbl_owners_individual where id in >> (83,175,111,1) order by str_last_name; >> >> id | str_last_name >> -----+---------------------- >> 83 | GXXXXXXXXX >> 175 | GXXXXXXXXX >> 1 | Kolesnik >> 111 | Kolesnik >> (4 ******) > > No, it didn't go out of its way to sort that way, it just happened > to fall out that way that time; don't count on it always being > that way, even if it happens many times in a row. > > test=# create table tbl_owners_individual > test-# (id int not null primary key, str_last_name text not > null); > CREATE TABLE > test=# insert into tbl_owners_individual values > test-# (1, 'Kolesnik'), > test-# (83, 'GXXXXXXXXX'), > test-# (111, 'Kolesnik'), > test-# (175, 'GXXXXXXXXX'); > INSERT 0 4 > test=# select id, str_last_name from tbl_owners_individual > test-# where id in (83,175,111,1) order by str_last_name; > id | str_last_name > -----+--------------- > 83 | GXXXXXXXXX > 175 | GXXXXXXXXX > 1 | Kolesnik > 111 | Kolesnik > (4 rows) Dang! I missed the tail of that session, which was the part that made my point. If you follow the above with a VACUUM ANALYZE and then run the same query again, you get a different order: test=# vacuum analyze tbl_owners_individual; VACUUM test=# select id, str_last_name from tbl_owners_individual test-# where id in (83,175,111,1) order by str_last_name; id | str_last_name -----+--------------- 175 | GXXXXXXXXX 83 | GXXXXXXXXX 111 | Kolesnik 1 | Kolesnik (4 rows) With better statistics from the VACUUM ANALYZE it realized that the index usage was pointless and slower, so it just used a table scan. -Kevin
-- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
