Re: browsing table with 2 million records - Mailing list pgsql-performance

From Merlin Moncure
Subject Re: browsing table with 2 million records
Date
Msg-id 6EE64EF3AB31D5448D0007DD34EEB3417DD6D2@Herge.rcsinc.local
Whole thread Raw
In response to browsing table with 2 million records  (aurora <aurora00@gmail.com>)
Responses Re: browsing table with 2 million records  (PFC <lists@boutiquenumerique.com>)
List pgsql-performance
Christopher
> >     - 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
>
> All very well and good, but now do it generically...

I've done it...
First of all I totally agree with PFC's rant regarding absolute
positioning while browsing datasets.  Among other things, it has serious
problems if you have multiple updating your table.  Also it's kind of
silly to be doing this in a set based data paradigm.

The 'SQL' way to browse a dataset is by key.  If your key has multiple
parts or you are trying to sort on two or more fields, you are supposed
to use the row constructor:

select * from t where (x, y) > (xc, yc) order by x,y;

Unfortunately, this gives the wrong answer in postgresql :(.

The alternative is to use boolean logic.  Here is a log snippit from my
ISAM driver (in ISAM, you are *always* browsing datasets):

prepare system_read_next_menu_item_favorite_file_0 (character varying,
int4, int4, int4)
    as select from system.menu_item_favorite_file
    where mif_user_id >= $1 and
        (mif_user_id >  $1 or  mif_menu_item_id >= $2) and
        (mif_user_id >  $1 or  mif_menu_item_id >  $2 or
mif_sequence_no >  $3)
    order by mif_user_id, mif_menu_item_id, mif_sequence_no
    limit $4

This is a Boolean based 'get next record' in a 3 part key plus a
parameterized limit.  You can do this without using prepared statements
of course but with the prepared version you can at least do

execute system_read_next_menu_item_favorite_file_0('abc', 1, 2, 1);

Merlin


pgsql-performance by date:

Previous
From: Richard Huxton
Date:
Subject: Re: Perfomance of views
Next
From: Jan Wieck
Date:
Subject: Re: Perfomance of views