Re: LIMIT clause and long timings

Thank very much for answers to my preceding question. I have obtained a
plain CSV file from MySQL and I have loaded my PostgreSQL table with
this file using the COPY command.

I have another question. Now I have a table in PostgreSQL with about
35000 records. The table has the following fields (sorry, names are in
italian!):
abi char(5) NOT NULL,
cab char(5) NOT NULL,
banca char(80) NOT NULL,
filiale char(60) NOT NULL,
indirizzo char(80) NOT NULL,
citta char(40) NOT NULL,
cap char(16) NOT NULL,
There is a primary key ('abi','cab') and an index for field 'banca'.
This table contains the list of all italian banks.

Note, I have the same table also on MySQL because my intention is to
test and understand better some SELECT benchmarks using both databases.

On PostgreSQL I have tried:
SELECT * FROM banche ORDER BY banca LIMIT 10 OFFSET 0;
[....]
(10 rows)

Time: 10,000 ms

Then I have tried:
SELECT * FROM banche ORDER BY banca LIMIT 10 OFFSET 34000;
[....]
(10 rows)

Time: 2433,000 ms

Why do I get this big timing??? I got similar timings also with MySQL. I
can think (or better I suppose) a database, in this situation, has to do
several filterings and seekings to reach the request offset. Is my
'intuition' correct?

My final target is to create a graphical Java application which uses
databases using JDBC. I would like, for example, to use a JTable to show
a database table in a tabular form.
With this (long) timings I can't obtain good performances! Especially
when I am at the bottom of the table.

What do you think? Is my approach correct??
Thank.

Andrea <andrea(dot)b73(at)email(dot)it> writes:
> Then I have tried:
> SELECT * FROM banche ORDER BY banca LIMIT 10 OFFSET 34000;
> [....]
> (10 rows)

> Time: 2433,000 ms

> Why do I get this big timing???

Because the system has to scan through 34000 rows before it gets to the
ten you asked for. OFFSET is not some bit of black magic, it just
causes the executor to discard the first N rows it's computed rather
than sending them over to the client.

regards, tom lane

would it be faster to use
SELECT * FROM banche ORDER BY banca DESC LIMIT 10 OFFSET 0;

the sorting is done anyway, so at least we don't have to scan through
the table to get the last 10 records?
or is sorting ASC less timeconsuming than sorting DESC?

regards, christoph

Tom Lane schrieb:
> Andrea <andrea(dot)b73(at)email(dot)it> writes:
>
>>Then I have tried:
>>SELECT * FROM banche ORDER BY banca LIMIT 10 OFFSET 34000;
>>[....]
>>(10 rows)
>
>
>>Time: 2433,000 ms
>
>
>>Why do I get this big timing???
>
>
> Because the system has to scan through 34000 rows before it gets to the
> ten you asked for. OFFSET is not some bit of black magic, it just
> causes the executor to discard the first N rows it's computed rather
> than sending them over to the client.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>