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

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 (view raw or flat)
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

pgsql-novice by date

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

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