LIMIT clause and long timings

From: Andrea <andrea(dot)b73(at)email(dot)it>
To: pgsql-novice(at)postgresql(dot)org
Subject: LIMIT clause and long timings
Date: 2006-03-28 12:21:37
Message-ID: 44292A51.3010203@email.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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.

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Guido Barosio 2006-03-28 12:22:39 Re: Transfer from MySQL to PostgreSQL
Previous Message Christoph Della Valle 2006-03-28 11:20:09 Re: delete from joined tables