Skip site navigation (1) Skip section navigation (2)

Re: browsing table with 2 million records

From: PFC <lists(at)boutiquenumerique(dot)com>
To: aurora <aurora00(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: browsing table with 2 million records
Date: 2005-10-26 21:49:54
Message-ID: op.sy9v9gketh1vuj@localhost (view raw or flat)
Thread:
Lists: pgsql-performance
> 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

	












In response to

Responses

pgsql-performance by date

Next:From: Thomas F. O'ConnellDate: 2005-10-26 22:48:17
Subject: Re: tuning seqscan costs
Previous:From: Steinar H. GundersonDate: 2005-10-26 21:37:30
Subject: Re: Materializing a sequential scan

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group