Postgresql pagination

Question

I came to know that PostgreSQL pagination (using LIMIT and OFFSET) is damn slow, unfortunately, its very late to me to understand this, because one of my small web app project uses it as the DB and the development is almost over.

I have seen a few workarounds in SO and other sites, but all of them uses extra indexes and things like that. But it does not suit me because I will have to create many indexes (there are a few dozens of tables) for the tables and will also have to rewrite the queries. So my question is :

Is there a way I can avoid this without spending more time (creating those extra indexes and rewriting query, etc) ? I mean, a simple workaround ?
I have not felt this slow with MySQL (and I guess, so is Oracle), hence, will PostgreSQL people be improving that in future versions ? or won't they do it at all ? (If they have a plan, I would like to keep on with PostgreSQL, if not I would go for MySQL, because a typical web app/ERP will have a lot of pagination requirements, and it is not wise to do extra work for this basic need)

You can increase work_mem setting to allow for more in-memory sorting. But it is crucial on any database system to figure out which indexes would be beneficial and implementing them. No database will think for you - it's your job. — Tometzky
– Tometzky, Commented Aug 7, 2015 at 21:35
work_mem won´t help much for huge table, unless you set it to huge number, but then it will let to consume much memory for EVERY other sorting — Vao Tsun
– Vao Tsun, Commented Aug 8, 2015 at 7:58
More information is needed, like datamodel, query and the results from EXPLAIN ANALYZE on the slow and fast (!) queries involved. Pagination can be very fast, but a slow query will never be fast... — Frank Heikens
– Frank Heikens, Commented Aug 8, 2015 at 8:01
Check out this link: chrisdone.com/posts/postgresql-pagination — FuzzY
– FuzzY, Commented Feb 9, 2016 at 5:40

Community · Accepted Answer · 2017-05-23 10:34:04Z

1

pagination?.. if you mean LIMIT OFFSET it is quite fast. Comparing to LIMIT WHERE ROWNUM of Oracle, LIMIT N,O in mysql... Quite same in all aspects

So answers would be:

Without spending time - nothing will happen
Things are being improved in every version. They will do some.

NB

edited May 23, 2017 at 10:34

CommunityBot

11 silver badge

answered Aug 7, 2015 at 21:07

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sibnick Over a year ago

LIMIT 10 OFFSET 10000000 means that postgres fetchs 10000000 rows and ignore them. As result everything is fine only until you use small offset.

Vao Tsun Over a year ago

good point. all three are same in regard of sequential reads.

Collectives™ on Stack Overflow

Postgresql pagination

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related