"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Denis Kolesnik <lirex(dot)software(at)gmail(dot)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