From: PFC <lists@boutiquenumerique.com>
To: aurora <aurora00@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: browsing table with 2 million records
Date: Wed, 26 Oct 2005 23:49:54 +0200
Message-ID: <op.sy9v9gketh1vuj@localhost> (raw)
In-Reply-To: <cbd177510510261341l4ed7a214lda9d67af12f2ec21@mail.gmail.com>
References: <cbd177510510261341l4ed7a214lda9d67af12f2ec21@mail.gmail.com>
> I am running Postgre 7.4 on FreeBSD. The main table have 2 million record
> (we would like to do at least 10 mil or more). It is mainly a FIFO
> structure
> with maybe 200,000 new records coming in each day that displace the older
> records.
I'm so sorry, but I have to rant XDDD
People who present a list of 100 items, paginated with 10 items per page
so that it fits on half a 800x600 screen should be shot.
I can scroll with my mousewheel and use text search in my browser...
People who present a paginated view with 100.000 pages where you have to
apply bisection search by hand to find records starting with "F" are on
page 38651 should be forced to use a keyboard with just 1 key and type in
morse code.
Problem of pagination is that the page number is meaningless and rather
useless to the user. It is also meaningless to the database, which means
you have to use slow kludges like count() and limit/offset. And as people
insert stuff in the table while you browse, when you hit next page you
will see on top, half of what was on the previous page, because it was
pushed down by new records. Or you might miss records.
So, rather than using a meaningless "record offset" as a page number, you
can use something meaningful, like a date, first letter of a name, region,
etc.
Of course, MySQL, always eager to encourage sucky-sucky practices,
provides a neat CALC_FOUND_ROWS hack, which, while not being super SQL
standard compliant, allows you to retrieve the number of rows the query
would have returned if you wouldn't have used limit, so you can compute
the number of pages and grab one page with only one query.
So people use paginators instead of intelligent solutions, like
xmlhttp+javascript enabled autocompletion in forms, etc. And you have to
scroll to page 38651 to find letter "F".
So if you need to paginate on your site :
CHEAT !!!!
Who needs a paginated view with 100.000 pages ?
- Select min(date) and max(date) from your table
- Present a nifty date selector to choose the records from any day, hour,
minute, second
- show them, with "next day" and "previous day" buttons
- It's more useful to the user (most likely he wants to know what
happened on 01/05/2005 rather than view page 2857)
- It's faster (no more limit/offset ! just "date BETWEEN a AND b",
indexed of course)
- no more new items pushing old ones to the next page while you browse
- you can pretend to your boss it's just like a paginated list
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@boutiquenumerique.com, aurora00@gmail.com
Subject: Re: browsing table with 2 million records
In-Reply-To: <op.sy9v9gketh1vuj@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox