My use-case is just creating paginated list for a large table.
The first obvious option is offset limit but it works too slow for great
offset.
A lot of topics propose using cursors for that, so I am learning this
possibility.
You will say that there are other possibilities. Yes - but I am trying
David Johnston wrote
Your original examples only create the cursor and do not actually use it.
You should be comparing how long it takes both examples to fetch the first
10 pages of records to get a meaningful comparison. It won't matter if
the DECLARE only takes 3ms in the non-hold case if
I wonder how quick is cursor without hold.
I have a data set with 10^7 rows.
*begin;
declare mycursor cursor for select * from z;
commit;
* - this takes 3 ms.
*begin;
declare mycursor cursor with hold for select * from z;
commit;
* - this takes 3 s.
Thus, holdable cursor is getting
This seems to be fixed in 9.2.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Index-scan-and-bitmap-index-scan-hard-to-understand-how-planner-chooses-tp5760304p5760637.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via
I firstly used 9.1.
After switching to 9.2. - the problem was fixed even without index only
scan.
I added another column to my table so this wasn't index only, but still had
better index behaviour.
--
View this message in context:
Here is a script for initializing my database
*drop table if exists z;
create table z as select lpad(i::text,6,'0') as name from
generate_series(0,99) as i;
create index z_name on z(name text_pattern_ops);
analyze;
*
So - I have a table z with one column name that has textual index.
Now, I