From: PFC <lists@boutiquenumerique.com>
To: Thomas Kellerer <spam_eater@gmx.net>
To: pgsql-sql@postgresql.org
Subject: Re: SELECT very slow
Date: Tue, 14 Jun 2005 14:26:21 +0200
Message-ID: <op.ssc0t7wrth1vuj@localhost> (raw)
In-Reply-To: <d87ood$8vq$1@sea.gmane.org>
References: <d87ood$8vq$1@sea.gmane.org>
> The problem is, that a SELECT * FROM foobar; takes ages (roughly 3
> minutes) to return the first row. I played around with the fetchSize()
> to disable the result set caching in the Java program first (before I
> tried psql) but that did not change anything.
Hello,
Yours seemed strange so I tried this :
Created a table with 128K lines, 4 TEXT columns containing about 70 chars
each...
---------------------------------------------
\d bigtest;
Colonne | Type | Modificateurs
---------+---------+---------------------------------------------------------
id | integer | not null default
nextval('public.bigtest_id_seq'::text)
data1 | text |
data2 | text |
data3 | text |
data4 | text |
Index :
«bigtest_pkey» PRIMARY KEY, btree (id)
---------------------------------------------
SELECT count(*) from bigtest;
count
--------
131072
---------------------------------------------
explain analyze select * from bigtest;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Seq Scan on bigtest (cost=0.00..7001.72 rows=131072 width=308) (actual
time=0.035..484.249 rows=131072 loops=1)
Total runtime: 875.095 ms
So grabbing the data takes 0.875 seconds.
---------------------------------------------
SELECT avg(length(data1)),
avg(length(data2)),avg(length(data3)),avg(length(data3)) from bigtest;
avg | avg | avg |
avg
---------------------+---------------------+---------------------+---------------------
72.1629180908203125 | 72.2342376708984375 | 72.3680572509765625 |
72.3680572509765625
Here you see the average data sizes.
---------------------------------------------
Now I fire up python, do a SELECT * from the table and retrieve all the
data as native objects... Hm, it takes about 1.3 seconds... on my
Pentium-M 1600 laptop...
I was about to suggest you use a less slow and bloated language than
Java, but then on my machine psql takes about 5 seconds to display the
results, so it looks like it ain't Java. psql is slow because it has to
format the result and compute the column widths.
Don't you have a problem somewhere ? Are you sure it's not swapping ? did
you check memory ? Are you transferring all this data over the network ?
Might an obscure cabling problem have reverted your connection to 10 Mbps ?
I'm using pg 8.0.something on Linux.
Ouch. I saw you're on Windows so I tried it on the windows machine there
which has a postgres installed, over a 100Mbps network, querying from my
linux laptop. The windows machine is a piece of crap, Pentium-II 300 and
256 MB Ram, it takes 7 seconds to retrieve the whole table in a python
native object.
So...
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lists@boutiquenumerique.com, spam_eater@gmx.net
Subject: Re: SELECT very slow
In-Reply-To: <op.ssc0t7wrth1vuj@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox