Thread: limit /offset
When i checkout the plan for this statement
select * from detail2 where id>125000 order by info1 limit 10
is:
Limit (cost=0.00..1.85 rows=10 width=52)
-> Index Scan using detail2_ind1 on detail2 (cost=0.00..61.50 rows=333
width=52)
and it takes less than a second to complete
but when i use offset in the same statement
select * from detail2 where id>125000 order by info1 limit 10 offset
10000
the plan changes
Limit (cost=36.47..36.47 rows=1 width=52)
-> Sort (cost=36.47..36.47 rows=333 width=52)
-> Seq Scan on detail2 (cost=0.00..22.50 rows=333 width=52)
and it takes almost 50 seconds to complete (42.890 sec)
Can anyone explain why offset doesn't use the index available?
the table is created as folows:
create table detail2
(id int8 not null primary key
,id_ref int8 references detail1 on delete cascade
,Info1 VarChar(15) not null
,Info2 VarChar(200)
,Info3 VarChar(200));
create index detail2_ind1 on detail2(Info1);
create index detail2_ind2 on detail2(Info2);
create index detail2_ind3 on detail2(Info3);
I think this query should use _detail2_in1_ in both cases.
Best regards,
Aplication Developer
Pirtea Calin Iancu
S.C. SoftScape S.R.L.
pcalin@rdsor.ro
On Sat, 16 Mar 2002, Pirtea Calin wrote: > When i checkout the plan for this statement > select * from detail2 where id>125000 order by info1 limit 10 > is: > Limit (cost=0.00..1.85 rows=10 width=52) > -> Index Scan using detail2_ind1 on detail2 (cost=0.00..61.50 rows=333 > width=52) > and it takes less than a second to complete > but when i use offset in the same statement > select * from detail2 where id>125000 order by info1 limit 10 offset > 10000 > the plan changes > Limit (cost=36.47..36.47 rows=1 width=52) > -> Sort (cost=36.47..36.47 rows=333 width=52) > -> Seq Scan on detail2 (cost=0.00..22.50 rows=333 width=52) > and it takes almost 50 seconds to complete (42.890 sec) > Can anyone explain why offset doesn't use the index available? > How many rows does detail2 have? What does explain say for the query and how long does it take if you set enable_seqscan=off before it?
> > but when i use offset in the same statement > > select * from detail2 where id>125000 order by info1 limit 10 offset > > 10000 > > the plan changes > > Limit (cost=36.47..36.47 rows=1 width=52) > > -> Sort (cost=36.47..36.47 rows=333 width=52) > > -> Seq Scan on detail2 (cost=0.00..22.50 rows=333 width=52) > > and it takes almost 50 seconds to complete (42.890 sec) > > Can anyone explain why offset doesn't use the index available? > > > > How many rows does detail2 have? What does explain say for the query and > how long does it take if you set enable_seqscan=off before it? > It doesn't change the explain when i try set enable_seqscan=off; select * from detail2 where id>125000 order by info1 limit 10 offset 10000; And it still takes 42+ seconds the table has 250000 records. Thanks in advance. Best regards, Aplication Developer Pirtea Calin Iancu S.C. SoftScape S.R.L. pcalin@rdsor.ro
On Sat, Mar 16, 2002 at 12:13:41PM +0200, Pirtea Calin wrote: > When i checkout the plan for this statement > select * from detail2 where id>125000 order by info1 limit 10 > is: > Limit (cost=0.00..1.85 rows=10 width=52) > -> Index Scan using detail2_ind1 on detail2 (cost=0.00..61.50 rows=333 > width=52) > and it takes less than a second to complete [snip] > Can anyone explain why offset doesn't use the index available? What makes you think that an index makes it faster to lookup the 10,000th row in a table? A index indexes on *values* and has nothing to do with the row numbers. So your query has to go through and check the 10,000 rows to ensure they actually match your query. That's what takes the time. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Ignorance continues to thrive when intelligent people choose to do > nothing. Speaking out against censorship and ignorance is the imperative > of all intelligent people.
> What makes you think that an index makes it faster to lookup the 10,000th row > in a table? A index indexes on *values* and has nothing to do with the row > numbers. So your query has to go through and check the 10,000 rows to ensure > they actually match your query. That's what takes the time. > I understand now. I think i hoped that offset uses the index to jump to the position 10000 (like jumping to a position in a file where the file is the index) and start sending rows from that point only. It was a stupid query anyway. Thank you. Best regards, Aplication Developer Pirtea Calin Iancu S.C. SoftScape S.R.L. pcalin@rdsor.ro