Josh,
Thanks for your feedback, I appreciate it.
Check what I have to say at http://www.powerpostgresql.com/PerfList
Will do.
They're currently on a two-disk Adaptec RAID1 with Postgresql 7.4.2.
And you've not upgraded to 7.4.6 because ?
Because the proprietary application running
Peter Darley wrote:
Folks,
I'm using PostgreSQL 7.4.1 on Linux, and I'm trying to figure out
weather a
query I have is going to be slow when I have more information in my tables.
both tables involved will likely have ~500K rows within a year or so.
Specifically I can't tell if I'm
Ühel kenal päeval (teisipäev, 25. jaanuar 2005, 10:41-0500), kirjutas
Tom Lane:
Hannu Krosing [EMAIL PROTECTED] writes:
Why is removing index entries essential ?
Because once you re-use the tuple slot, any leftover index entries would
be pointing to the wrong rows.
That much I understood
Peter Darley wrote:
Folks,
I'm using PostgreSQL 7.4.1 on Linux
Oh, and move to the latest in the 7.4 series too.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
The problems still stays open.
The thing is that I have about 20 - 30 clients that are using that SQL query
where the offset and limit are involved. So, I cannot create a temp table,
because that means that I'll have to make a temp table for each session...
which is a very bad ideea. Cursors
Hi,
What you could do is create a table containing all the fields from your SELECT,
plus a per-session unique ID. Then you can store the query results in there,
and use SELECT with OFFSET / LIMIT on that table. The WHERE clause for this
temp-results table only needs to contain the per-session
As I read the docs, a temp table doesn't solve our problem, as it does
not persist between sessions. With a web page there is no guarentee
that you will receive the same connection between requests, so a temp
table doesn't solve the problem. It looks like you either have to
create a real table
Hannu Krosing [EMAIL PROTECTED] writes:
But can't clearing up the index be left for later ?
Based on what? Are you going to store the information about what has to
be cleaned up somewhere else, and if so where?
Indexscan has to check the data tuple anyway, at least for visibility.
would
Richard,
I tried a left join, which has to be a little weird, because there may
or
may not be a corresponding row in Assignment_Settings for each Assignment,
and they may or may not have Setting='Status', so I came up with:
SELECT User_ID
FROM Assignments A NATURAL LEFT JOIN (SELECT *
Supposing your searches display results which are rows coming from one
specific table, you could create a cache table :
search_id serial primary key
index_n position of this result in the global result set
result_id id of the resulting row.
Then, making a search with 50k results
Does anybody know where I can lay my hands on some guidelines to get best SQL
performance
out of PostgreSQL? We are about to get into a project that will be new from the
ground up (and\we are using Postgres for the first time). Would like to share
some guidelines with developers on best
Steve,
Because the proprietary application running the business has not
certified on it. Unfortunately, I am at the mercy of their support in
case something goes wrong.
FWIW, 7.4.6 is a binary, drop-in place upgrade for 7.4.2. And 7.4.2 has known
bugs. However, I understand your
Clarification: I am talking about SQL coding practices in Postgres (how to
write queries for best
results), not tuning-related considerations (although that would be welcomed
too).
-Original Message-
From: [EMAIL PROTECTED] on behalf of Van Ingen, Lane
Sent: Wed 1/26/2005 11:44 AM
The problem with this approach is TTFB (Time to first Byte). The
initial query is very slow, but additional requests are fast. In most
situations we do not want the user to have to wait a disproportionate
amount of time for the initial query. If this is the first time using
the system this will
Title: Message
Will I have to dump
and reload all my databases when migrating from 7.4.2 to
8.0?
Jim Gunzelman
Senior Software
Engineer
phone: 402.361.3078 fax: 402.361.3178
e-mail:
JamesGunzelman[EMAIL PROTECTED]
Solutionary,
Inc.
The problem with this approach is TTFB (Time to first Byte). The
initial query is very slow, but additional requests are fast. In most
situations we do not want the user to have to wait a disproportionate
amount of time for the initial query. If this is the first time using
the system this
Just a quick shout-out to Mark, as you provided the winning answer. I
found numerous mailing list discussions and web pages, but all were
either fragmented or out of date.
Again, many thanks!
-- Mitch
On Wed, 26 Jan 2005 10:08:58 +1300, Mark Kirkwood [EMAIL PROTECTED] wrote:
in
http://borg.postgresql.org/docs/8.0/interactive/storage-page-layout.html
If you vacuum as part of the transaction it's going to be more efficient
of resources, because you have more of what you need right there (ie:
odds are that you're on the same page as the old tuple). In cases like
that it
James Gunzelman [EMAIL PROTECTED] writes:
Will I have to dump and reload all my databases when migrating from
7.4.2 to 8.0?
Yes.
-Doug
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Alex Turner [EMAIL PROTECTED] writes:
The problem with this approach is TTFB (Time to first Byte). The
initial query is very slow, but additional requests are fast. In most
situations we do not want the user to have to wait a disproportionate
amount of time for the initial query. If this
On Wed, Jan 26, 2005 at 12:51:14PM -0600, James Gunzelman wrote:
Will I have to dump and reload all my databases when migrating from
7.4.2 to 8.0?
Yes -- the Release Notes mention it under Migration to version 8.0:
http://www.postgresql.org/docs/8.0/static/release.html#RELEASE-8-0
Those
Every time I tested an idea to speed it up, I got exactly the same
loading time on a Athlon 1800+, 256Mb RAM, 20Gb PATA computer as
compared to a Dual Opteron 246, 1Gb RAM, 70Gb WD Raptor SATA server.
Now, why a dual opteron machine can't perform any faster than a lowly
1800+ athlon in
It should be noted that users who use Slony can create a subscriber
node running 8.0 that subscribes to a node running 7.4.x and can
transition with only the downtime required for failover.
This obviates the need for a dump/restore.
See http://slony.info/.
-tfo
--
Thomas F. O'Connell
FWIW, 7.4.6 is a binary, drop-in place upgrade for 7.4.2. And 7.4.2 has known
bugs. However, I understand your situation.
As soon as we get the go-ahead, I will upgrade. I think the company is
actually looking towards 8.0 certification.
Okay, thanks. Even with 7-disks? I trust that.
with about 8000 rows. For this table query:
SELECT MAX(MsgSeqNum),MAX(LogTimestamp) FROM ELT_tcli_MessageLog
WHERE LogTimestamp = '0' AND IsFromCounterParty = 'Y' AND
IsOutOfSequence = 'N'
AND ConnectionName = 'DB_BENCHMARK'
AND LogTimestamp IN (SELECT MAX(LogTimestamp)
The problem with this approach is TTFB (Time to first Byte). The
initial query is very slow, but additional requests are fast. In most
situations we do not want the user to have to wait a disproportionate
amount of time for the initial query. If this is the first time using
the system this will
Steve Poe [EMAIL PROTECTED] writes:
Well, it's less bad with 7 disks than it is with 3, certainly.
However,there
is an obvious and quick gain to be had by splitting off the WAL logs onto
their own disk resource ... up to 14%+ performance in some applications.
Pardon my ignorance, but
Short summary... the second query runs faster, and I think
they should be identical queries. Should the optimizer
have found this optimization?
I have two identical (or so I believe) queries; one where I
explicitly add a is not null comparison; and one where I
think it would implicitly only
Ron Mayer [EMAIL PROTECTED] writes:
Should the optimizer have found this optimization?
I can't get excited about it. Joining on a column that's mostly nulls
doesn't seem like a common thing to do.
regards, tom lane
---(end of
Steve,
Okay. InCPU-bound servers, use hw RAID. Any hw raids to avoid?
Well, the list of ones which are good is shorter: pretty much LSI and 3Ware
(for SATA). You can suffer with Adaptec if you have to.
If we went with a single CPU, like Athlon/Opertron64, would CS
storming go away?
Yes.
30 matches
Mail list logo
