Christian Paul Cosinas wrote:
Hi!
How can I speed up my server's performance when I use offset and limit
clause.
For example I have a query:
SELECT * FROM table ORDER BY id, name OFFSET 10 LIMIT 1
This query takes a long time about more than 2 minutes.
If my query is:
SELECT * FROM ta
Hi!
How can I speed up my server's performance when I use offset and limit
clause.
For example I have a query:
SELECT * FROM table ORDER BY id, name OFFSET 10 LIMIT 1
This query takes a long time about more than 2 minutes.
If my query is:
SELECT * FROM table ORDER BY id, name OFFSET 500
Brian Wipf <[EMAIL PROTECTED]> writes:
> I'm trying to determine why an identical query is running
> approximately 500 to 1000 times slower on our production database
> compared to our backup database server.
It looks to me like it's pure luck that the query is fast on the backup
server. The
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> On Tue, May 09, 2006 at 03:13:01PM -0400, Tom Lane wrote:
>> PFC <[EMAIL PROTECTED]> writes:
>>> Fun thing is, the rowcount from a temp table (which is the problem here)
>>> should be available without ANALYZE ; as the temp table is not concurrent,
>
I added to the exists query qualifier: AND cp.category_id = 1000962
(in addition to the cp.category_id = ac.category_id)
Now I am getting a much better query plan on our production server:
Index Scan using attribute_category__category_id_fk_idx on
attribute_category ac (cost=0.00..485.71 ro
Kris Jurka wrote:
On Wed, 10 May 2006, Thomas Vatter wrote:
Yes, the difference between psql command line and application is 6
seconds to 40 seconds. It is exactly the step resultSet =
excecuteQuery() that needs 40 seconds. I use next() as a cursor
through the resultSet, but I fear this is
I'm trying to determine why an identical query is running
approximately 500 to 1000 times slower on our production database
compared to our backup database server.
Both database servers are dual 2.3 GHz G5 Xserves running PostgreSQL
8.1.3; both are configured with 8GB of RAM with identical
On Wed, May 10, 2006 at 01:10:51PM -0500, Bruno Wolff III wrote:
> On Wed, May 10, 2006 at 01:15:11 -0400,
> Tom Lane <[EMAIL PROTECTED]> wrote:
> >
> > Maybe the real problem is at the other end of the process, ie we should
> > require some evidence of a greater-than-room-temp IQ to subscribe i
On Wed, May 10, 2006 at 11:10:37AM -0400, Tom Lane wrote:
> Michael Glaesemann <[EMAIL PROTECTED]> writes:
> > (And are there mail readers out there that can pick those subscribe/
> > unsubscribe headers from the list emails? Now *that'd* be sweet.)
>
> Well, in my fairly ancient copy of exmh, an
On Wed, 10 May 2006, Thomas Vatter wrote:
Yes, the difference between psql command line and application is 6
seconds to 40 seconds. It is exactly the step resultSet =
excecuteQuery() that needs 40 seconds. I use next() as a cursor through
the resultSet, but I fear this is not enough, do I ha
On Thu, May 04, 2006 at 04:45:57PM +0200, Mario Splivalo wrote:
Well, here's the problem...
> -> Nested Loop (cost=0.00..176144.30 rows=57925 width=26)
> (actual time=1074.984..992536.243 rows=57925 loops=1)
>-> Seq Scan on ticketing_codes_played
> (cost=0.00..863.25 ro
Scott Marlowe wrote:
On Wed, 2006-05-10 at 15:54, Thomas Vatter wrote:
Yes, the difference between psql command line and application is 6
seconds to 40 seconds. It is
exactly the step resultSet = excecuteQuery() that needs 40 seconds. I
use next() as a cu
Title: Message
Dave Dutcher wrote:
Are you using the Postgres JDBC driver? Or
are you using an ODBC JDBC driver? The Postgres specific driver is
usually faster.
I'm using the postgres driver
regards
tom
-Original Message-
From: [EMAIL PROTECTED]
Title: Message
Are
you using the Postgres JDBC driver? Or are you using an ODBC JDBC
driver? The Postgres specific driver is usually
faster.
-Original Message-From:
[EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Thomas
VatterSent: Wednesday, May 10, 2006
On Wed, 2006-05-10 at 15:54, Thomas Vatter wrote:
> >
> Yes, the difference between psql command line and application is 6
> seconds to 40 seconds. It is
> exactly the step resultSet = excecuteQuery() that needs 40 seconds. I
> use next() as a cursor
> through the resultSet, but I fear this is
Scott Marlowe wrote:
On Wed, 2006-05-10 at 10:41, Thomas Vatter wrote:
Scott Marlowe wrote:
What happens if you do this by declaring it as a cursor and then
fetching the first row?
I do executeQuery(
On Tue, May 09, 2006 at 06:29:31PM +0200, PFC wrote:
You mean the cursors'storage is in fact the same internal machinery
as a temporary table ?
Use the source, Luke...
LOL, yeah, I should have, sorry.
See tuplestore_begin_heap in backend/utils/sort/tuplestore.c an
On Tue, May 09, 2006 at 06:29:31PM +0200, PFC wrote:
> You mean the cursors'storage is in fact the same internal machinery
> as a temporary table ?
Use the source, Luke...
See tuplestore_begin_heap in backend/utils/sort/tuplestore.c and
heap_create_with_catalog in backend/catalog/he
On Tue, May 09, 2006 at 01:29:56PM +0200, PFC wrote:
> 0.101 ms BEGIN
> 1.451 ms CREATE TEMPORARY TABLE tmp ( a INTEGER NOT NULL, b INTEGER NOT
> NULL, c TIMESTAMP NOT NULL, d INTEGER NOT NULL ) ON COMMIT DROP
> 0.450 ms INSERT INTO tmp SELECT * FROM bookmarks ORDER BY annonce_id DESC
> LIMIT 2
On Tue, May 09, 2006 at 03:13:01PM -0400, Tom Lane wrote:
> PFC <[EMAIL PROTECTED]> writes:
> > Fun thing is, the rowcount from a temp table (which is the problem
> > here)
> > should be available without ANALYZE ; as the temp table is not concurrent,
> > it would be simple to inc/decremen
On Tue, May 09, 2006 at 11:33:42AM +0200, PFC wrote:
> - Repeating the query might yield different results if records were
> added or deleted in the meantime.
BTW, SET TRANSACTION ISOLATION LEVEL serializeable or BEGIN ISOLATION
LEVEL serializeable would cure that.
--
Jim C. Nasby,
On Wed, May 10, 2006 at 01:15:11 -0400,
Tom Lane <[EMAIL PROTECTED]> wrote:
>
> Maybe the real problem is at the other end of the process, ie we should
> require some evidence of a greater-than-room-temp IQ to subscribe in the
> first place?
I suspect it is more lazyiness that smarts. That had
Hi, Nils,
Nis Jorgensen wrote:
> It will probably be quite common for the number to depend on the number
> of rows in other tables. Even if this is fairly constant within one db
> (some assumption), it is likely to be different in others using the same
> function definition. Perhaps a better solu
Martijn van Oosterhout wrote:
> On Wed, May 10, 2006 at 04:38:31PM +0200, PFC wrote:
>> You need to do some processing to know how many rows the function
>> would return.
>> Often, this processing will be repeated in the function itself.
>> Sometimes it's very simple (ie. the
is there a possibility for creating views or temp tables in memory to
avoid disk io when user makes select operations?
you might also want to look into "materialized views":
http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html
http://www.varlena.com/varlena/GeneralBits/64.php
On Wed, 2006-05-10 at 10:41, Thomas Vatter wrote:
> Scott Marlowe wrote:
> > What happens if you do this by declaring it as a cursor and then
> > fetching the first row?
> >
>
> I do executeQuery(), for the resultSet I do next() and return one row,
> but wait, I have to review the logic in t
Scott Marlowe wrote:
On Wed, 2006-05-10 at 04:55, Thomas Vatter wrote:
Tino Wildenhain wrote:
Thomas Vatter schrieb:
is there a possibility for creating views or temp tables in memory to
avoid disk io when user makes select operations?
I will try answering your questions. Please note that I am a newbie myself.
Clemens Eisserer wrote
> All primary keys are indixed, and this is what explain tells me:
> Unique (cost=15.67..16.69 rows=34 width=115)
> -> Sort (cost=15.67..15.75 rows=34 width=115)
> Sort Key: customer.em
On Wed, 2006-05-10 at 04:55, Thomas Vatter wrote:
> Tino Wildenhain wrote:
>
> > Thomas Vatter schrieb:
> >
> >> is there a possibility for creating views or temp tables in memory to
> >> avoid disk io when user makes select operations?
> >
> >
> > No need. The data will be available in OS and da
Michael Glaesemann <[EMAIL PROTECTED]> writes:
> (And are there mail readers out there that can pick those subscribe/
> unsubscribe headers from the list emails? Now *that'd* be sweet.)
Well, in my fairly ancient copy of exmh, any message with such headers
causes an additional menu to appear:
On Wed, 2006-05-10 at 09:51, Douglas McNaught wrote:
> Scott Marlowe <[EMAIL PROTECTED]> writes:
>
> > On Tue, 2006-05-09 at 20:02, Bruce Momjian wrote:
>
> >> You do if the controller thinks the data is already on the drives and
> >> removes it from its cache.
> >
> > Bruce, re-read what I wrote
Hi, PFC,
PFC wrote:
> You need to do some processing to know how many rows the function
> would return.
> Often, this processing will be repeated in the function itself.
> Sometimes it's very simple (ie. the function will RETURN NEXT each
> element in an array, you know the array le
Hi, Bruce,
Markus Schaber wrote:
>>>It does not find as much liers as the script above, but it is less
>>Why does it find fewer liers?
>
> It won't find liers that have a small "lie-queue-length" so their
> internal buffers get full so they have to block. After a small burst at
> start which usu
On Wed, May 10, 2006 at 04:38:31PM +0200, PFC wrote:
> You need to do some processing to know how many rows the function
> would return.
> Often, this processing will be repeated in the function itself.
> Sometimes it's very simple (ie. the function will RETURN NEXT each
Scott Marlowe <[EMAIL PROTECTED]> writes:
> On Tue, 2006-05-09 at 20:02, Bruce Momjian wrote:
>> You do if the controller thinks the data is already on the drives and
>> removes it from its cache.
>
> Bruce, re-read what I wrote. The escalades tell the drives to TURN OFF
> THEIR OWN CACHE.
Some
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of
> Clemens Eisserer
> Sent: Wednesday, May 10, 2006 6:50 AM
> To: pgsql-performance@postgresql.org
> Subject: [PERFORM] Question about explain-command...
>
> What does the hash-lines mean, does that
On Tue, 2006-05-09 at 20:02, Bruce Momjian wrote:
> Scott Marlowe wrote:
> > Actually, in the case of the Escalades at least, the answer is yes.
> > Last year (maybe a bit more) someone was testing an IDE escalade
> > controller with drives that were known to lie, and it passed the power
> > plug
Hi, Bruce,
Bruce Momjian wrote:
>>It does not find as much liers as the script above, but it is less
>
> Why does it find fewer liers?
It won't find liers that have a small "lie-queue-length" so their
internal buffers get full so they have to block. After a small burst at
start which usually h
The problem is that you need a set-returning function to retrieve
the values. SRFs don't have rowcount estimates, so the plans suck.
What about adding some way of rowcount estimation to SRFs, in the way of:
CREATE FUNCTION foo (para, meters) RETURNS SETOF bar AS
$$ ... function code ...
Vivek Khera wrote:
>
> On May 10, 2006, at 12:41 AM, Greg Stark wrote:
>
> > Well, dollar for dollar you would get the best performance from
> > slower drives
> > anyways since it would give you more spindles. 15kRPM drives are
> > *expensive*.
>
> Personally, I don't care that much for "dol
On Tue, May 09, 2006 at 03:19:08AM -0700, [EMAIL PROTECTED] wrote:
> I have got such problem.
> Im running Postgresql 7.3.2 on Linux 2.6.13.
> What is see when VACCUM is running and killing my CPU is:
>
> Cpu(s): 3.2% us, 0.0% sy, 0.0% ni, 0.0% id, 96.8% wa, 0.0% hi,
> 0.0% si
>
> what i am
On May 10, 2006, at 12:41 AM, Greg Stark wrote:
Well, dollar for dollar you would get the best performance from
slower drives
anyways since it would give you more spindles. 15kRPM drives are
*expensive*.
Personally, I don't care that much for "dollar for dollar" I just
need performance.
Markus Schaber wrote:
> Hi, Scott & all,
>
> Scott Lamb wrote:
>
> > I don't know the answer to this question, but have you seen this tool?
> >
> > http://brad.livejournal.com/2116715.html
>
> We had a simpler tool inhouse, which wrote a file byte-for-byte, and
> called fsync() after every
Hi, Scott & all,
Scott Lamb wrote:
> I don't know the answer to this question, but have you seen this tool?
>
> http://brad.livejournal.com/2116715.html
We had a simpler tool inhouse, which wrote a file byte-for-byte, and
called fsync() after every byte.
If the number of fsyncs/min is high
Hi, PFC,
PFC wrote:
> The problem is that you need a set-returning function to retrieve
> the values. SRFs don't have rowcount estimates, so the plans suck.
What about adding some way of rowcount estimation to SRFs, in the way of:
CREATE FUNCTION foo (para, meters) RETURNS SETOF bar AS
$$
* Hannes Dorbath:
> + Hardware Raids might be a bit easier to manage, if you never spend a
> few hours to learn Software Raid Tools.
I disagree. RAID management is complicated, and once there is a disk
failure, all kinds of oddities can occur which can make it quite a
challenge to get back a non
Tino Wildenhain wrote:
Thomas Vatter schrieb:
Tino Wildenhain wrote:
...
Well you will need the equally 40 seconds to fill your hypothetical
in memory table. (even a bit more due to the creation of a
datastructure).
So you can do the aproaches of semi materialized views (that are in
fa
Greg Stark <[EMAIL PROTECTED]> writes:
> Douglas McNaught <[EMAIL PROTECTED]> writes:
>> Correct me if I'm wrong, but I've never heard of a 15kRPM SATA drive.
>
> Well, dollar for dollar you would get the best performance from slower drives
> anyways since it would give you more spindles. 15kRPM
Thomas Vatter schrieb:
Tino Wildenhain wrote:
...
Well you will need the equally 40 seconds to fill your hypothetical
in memory table. (even a bit more due to the creation of a
datastructure).
So you can do the aproaches of semi materialized views (that are in fact
writing into a shadow tabl
Hello,
I just discovered the explain command and well ... have some (for you
of course very stupid) questions.
I do a quite large (for my taste) join, the query looks like the following:
SELECT DISTINCT customer.email AS cemail, customer.key AS ckey,
customer.anrede AS canrede, customer.strasse
Hi,
there was a similar discussion with a ramdisk:
http://archives.postgresql.org/pgsql-hackers/2005-11/msg01058.php
You need to populate the data on serverstart, of course.
But as Timo mentionend, it's maybe not worth the trouble.
Maybe their is a way to speed up the queriy itself.
To analyze
Tino Wildenhain wrote:
Thomas Vatter schrieb:
Tino Wildenhain wrote:
Thomas Vatter schrieb:
is there a possibility for creating views or temp tables in memory
to avoid disk io when user makes select operations?
No need. The data will be available in OS and database caches if
they are
Thomas Vatter schrieb:
Tino Wildenhain wrote:
Thomas Vatter schrieb:
is there a possibility for creating views or temp tables in memory to
avoid disk io when user makes select operations?
No need. The data will be available in OS and database caches if
they are really required often. If n
Tino Wildenhain wrote:
Thomas Vatter schrieb:
is there a possibility for creating views or temp tables in memory to
avoid disk io when user makes select operations?
No need. The data will be available in OS and database caches if
they are really required often. If not, tune up the caches an
Thomas Vatter schrieb:
is there a possibility for creating views or temp tables in memory to
avoid disk io when user makes select operations?
No need. The data will be available in OS and database caches if
they are really required often. If not, tune up the caches and
do a regular "pre select"
is there a possibility for creating views or temp tables in memory to
avoid disk io when user makes select operations?
regards
tom
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index
Hi,
>>What I am worry about is "93.5% wa" ...
>>
>>Could someone explain me what is the VACUUM process waiting for ?
>>
>
>
> Disk I/O.
>
CPU
wa: Time spent waiting for IO. Prior to Linux 2.5.41, shown as zero.
Just a little more info to help understand what Alan has pointed out.
Your CPU pr
57 matches
Mail list logo
