From: | "Alexei" <alexei_novakov(at)yahoo(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #4819: Ordering big tables by indexed columns is very slow. |
Date: | 2009-05-21 01:04:28 |
Message-ID: | 200905210104.n4L14Skq067255@wwwmaster.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged online:
Bug reference: 4819
Logged by: Alexei
Email address: alexei_novakov(at)yahoo(dot)com
PostgreSQL version: 8.3.7
Operating system: Open SuSE 11.1 AMD Athlon 64 X2
Description: Ordering big tables by indexed columns is very slow.
Details:
Hello.
I have very simple query, which runs very long when has "order by" clause,
even though all columns in "order by" are indexed. Here is the simplified
testcase.
1) Table:
create table tmp1
(
field1 bigint not null,
field2 integer not null
)
2) Data:
I generated some test data for this table: field2 is always 2; field1 starts
from 1242865824484 and every next one is incremented by 1. I generated 3
million records.
3) Index:
create index tmp1_idx on tmp1 (field1, field2)
4) Query:
select field1, field2 from tmp1 order by 1, 2
The query plan for this query is:
Sort (cost=522779.47..530279.47 rows=3000000 width=12)
Sort Key: field1, field2
-> Seq Scan on tmp1 (cost=0.00..46217.00 rows=3000000 width=12)
Index is not used for the sorting here. But if I add "limit 1000" in the end
I get the following:
Limit (cost=0.00..75.33 rows=100 width=12)
-> Index Scan using tmp1_idx on tmp1 (cost=0.00..2259857.96 rows=3000000
width=12)
If I increase limit to 700000 index is not used again and the difference in
execution time is very noticeable:
1 millisecond for "limit 600000"; and 6 seconds for "limit 700000"
Is there anything what can be configured to make it use the index for the
ordering?
Best Regards,
Alexei Novakov.
From | Date | Subject | |
---|---|---|---|
Next Message | Целуйко Дмитрий | 2009-05-21 07:52:33 | DDL triggers |
Previous Message | David Blewett | 2009-05-20 13:17:10 | Re: BUG #4817: Dump of 8.3 hstore not restorable to 8.4 (RECHECK) |