On 05.09.2007 01:15, Scott Marlowe wrote:
On 9/4/07, Alvaro Herrera [EMAIL PROTECTED] wrote:
Carlo Stonebanks wrote:
A client is moving their postgresql db to a brand new Windows 2003 x64
server with 2 quad cores and 32GB of RAM. It is a dedicated server to run
8.2.4.
Large shared_buffers and
Hi all,
I need to improve a query like :
SELECT id, min(the_date), max(the_date) FROM my_table GROUP BY id;
Stupidly, I create a B-tree index on my_table(the_date), witch is logically not
used in my query, because it's not with a constant ? isn't it ?
I know that I can't create a function
bad address kep his from going to the list on my first try ... apologies to the
moderators.
-Original Message-
From: Gregory Williamson
Sent: Wed 9/5/2007 4:59 AM
To: JS Ubei; pgsql-performance@postgresql.org
Subject: RE: [PERFORM] optimize query with a maximum(date) extraction
In
JS Ubei [EMAIL PROTECTED] writes:
Hi all,
I need to improve a query like :
SELECT id, min(the_date), max(the_date) FROM my_table GROUP BY id;
Stupidly, I create a B-tree index on my_table(the_date), witch is logically
not used in my query, because it's not with a constant ? isn't it ?
Gregory Stark [EMAIL PROTECTED] writes:
JS Ubei [EMAIL PROTECTED] writes:
I need to improve a query like :
SELECT id, min(the_date), max(the_date) FROM my_table GROUP BY id;
...
I don't think you'll find anything much faster for this particular query. You
could profile running these two
On 05/09/07, Gregory Stark [EMAIL PROTECTED] wrote:
Gregory Stark [EMAIL PROTECTED] writes:
JS Ubei [EMAIL PROTECTED] writes:
I need to improve a query like :
SELECT id, min(the_date), max(the_date) FROM my_table GROUP BY id;
...
I don't think you'll find anything much faster for
why not
select id,
min(the_date) as min_date,
max(the_date) as max_date
from my_table group by id;
Since 8.0 or was it earlier this will use an index should a reasonable one
exist.
without any limits, seq scan is optimal.
Regards
Pavel Stehule
Great idea !
with your second solution, my query seem to use the index on date. but the
global performance is worse :-(
I will keep th original solution !
Lot of thanks, Gregory
jsubei
- Message d'origine
De : Gregory Stark [EMAIL PROTECTED]
À : JS Ubei [EMAIL PROTECTED]
Cc :
On Wed, Sep 05, 2007 at 12:30:21PM +0100, Gregory Stark wrote:
SELECT DISTINCT ON (id) id, the_date AS min_date FROM my_table ORDER BY id,
the_date ASC
SELECT DISTINCT ON (id) id, the_date AS max_date FROM my_table ORDER BY id,
the_date DESC
I think the first of these can actually use your
hubert depesz lubaczewski [EMAIL PROTECTED] writes:
On Wed, Sep 05, 2007 at 12:30:21PM +0100, Gregory Stark wrote:
SELECT DISTINCT ON (id) id, the_date AS min_date FROM my_table ORDER BY id,
the_date ASC
SELECT DISTINCT ON (id) id, the_date AS max_date FROM my_table ORDER BY id,
the_date
Peter Childs [EMAIL PROTECTED] writes:
My personal reaction is why are you using distinct at all?
why not
select id,
min(the_date) as min_date,
max(the_date) as max_date
from my_table group by id;
Since 8.0 or was it earlier this will use an index should a reasonable one
Pavel Stehule [EMAIL PROTECTED] writes:
why not
select id,
min(the_date) as min_date,
max(the_date) as max_date
from my_table group by id;
Since 8.0 or was it earlier this will use an index should a reasonable one
exist.
As I mentioned in the other post that's not true
Unfortunately, LINUX is not an option at this time. We looked into it; there
is no *NIX expertise in the enterprise. However, I have raised this issue in
various forums before, and when pressed no one was willing to say that *NIX
*DEFINITELY* outperforms Windows for what my client is doing (or if
On 9/5/07, Carlo Stonebanks [EMAIL PROTECTED] wrote:
Unfortunately, LINUX is not an option at this time. We looked into it; there
is no *NIX expertise in the enterprise. However, I have raised this issue in
various forums before, and when pressed no one was willing to say that *NIX
Right, additionally NTFS is really nothing to use on any serious disc
array.
Do you mean that I will not see any big improvement if I upgrade the disk
subsystem because the client is using NTFS (i.e. Windows)
---(end of broadcast)---
TIP 9: In
Gregory Stark [EMAIL PROTECTED] writes:
You can simulate such a plan with the subqueries I described but
there's a bit more overhead than necessary and you need a reasonably
efficient source of the distinct ids.
Yeah, that seems like the $64 question. If you have no better way of
finding out
On 9/5/07, Carlo Stonebanks [EMAIL PROTECTED] wrote:
Right, additionally NTFS is really nothing to use on any serious disc
array.
Do you mean that I will not see any big improvement if I upgrade the disk
subsystem because the client is using NTFS (i.e. Windows)
No, I think he's referring
Large shared_buffers and Windows do not mix. Perhaps you should leave
the shmem config low, so that the kernel can cache the file pages.
Is there a problem BESIDES the one that used to cause windows to fail to
allocate memory in blocks larger than 1.5GB?
The symptom of this problem was that
Carlo Stonebanks wrote:
It sounds like you will need a huge lot of vacuuming effort to keep up.
Maybe you should lower autovac scale factors so that your tables are
visited more frequently. A vacuum_delay of 40 sounds like too much
though.
Does autovacuum not impede performance while
On 9/5/07, Carlo Stonebanks [EMAIL PROTECTED] wrote:
Large shared_buffers and Windows do not mix. Perhaps you should leave
the shmem config low, so that the kernel can cache the file pages.
Is there a problem BESIDES the one that used to cause windows to fail to
allocate memory in blocks
On 9/5/07, Scott Marlowe [EMAIL PROTECTED] wrote:
On 9/5/07, Carlo Stonebanks [EMAIL PROTECTED] wrote:
Right, additionally NTFS is really nothing to use on any serious disc
array.
Do you mean that I will not see any big improvement if I upgrade the disk
subsystem because the client is
Trevor Talbot wrote:
Lack of reliability compared to _UFS_? Can you elaborate on this?
What elaboration's needed? UFS seems to have one of the longest
histories of support from major vendors of any file system supported
on any OS (Solaris, HP-UX, SVR4, Tru64 Unix all use it).
Can you
On 9/5/07, Trevor Talbot [EMAIL PROTECTED] wrote:
On 9/5/07, Scott Marlowe [EMAIL PROTECTED] wrote:
On 9/5/07, Carlo Stonebanks [EMAIL PROTECTED] wrote:
Right, additionally NTFS is really nothing to use on any serious disc
array.
Do you mean that I will not see any big improvement
On 9/5/07, Trevor Talbot [EMAIL PROTECTED] wrote:
On 9/5/07, Scott Marlowe [EMAIL PROTECTED] wrote:
On 9/5/07, Carlo Stonebanks [EMAIL PROTECTED] wrote:
Right, additionally NTFS is really nothing to use on any serious disc
array.
Do you mean that I will not see any big improvement
On 9/5/07, Scott Marlowe [EMAIL PROTECTED] wrote:
On 9/5/07, Trevor Talbot [EMAIL PROTECTED] wrote:
On 9/5/07, Scott Marlowe [EMAIL PROTECTED] wrote:
On 9/5/07, Carlo Stonebanks [EMAIL PROTECTED] wrote:
Right, additionally NTFS is really nothing to use on any serious disc
array.
On 2007-09-05 Scott Marlowe wrote:
And there's the issue that with windows / NTFS that when one process
opens a file for read, it locks it for all other users. This means
that things like virus scanners can cause odd, unpredictable failures
of your database.
Uh... what? Locking isn't done by
On 9/5/07, Ansgar -59cobalt- Wiechers [EMAIL PROTECTED] wrote:
On 2007-09-05 Scott Marlowe wrote:
And there's the issue that with windows / NTFS that when one process
opens a file for read, it locks it for all other users. This means
that things like virus scanners can cause odd,
On Wed, 2007-09-05 at 14:36 -0500, Scott Marlowe wrote:
On 9/5/07, Trevor Talbot [EMAIL PROTECTED] wrote:
On 9/5/07, Scott Marlowe [EMAIL PROTECTED] wrote:
On 9/5/07, Carlo Stonebanks [EMAIL PROTECTED] wrote:
Right, additionally NTFS is really nothing to use on any serious disc
Hello All,
We have a postgres setup on solaris 10 with sun cluster for HA purposes.
2 nodes are configured in the cluster in active-passive mode with
pg_data stored on external storage. Everything is working as expected
however, when we either switch the resource group from one node to other
or
Hi
I couldnt find any specifics on this subject in the documentation, so I
thought I'd ask the group.
how does pg utilise multi cpus/cores, i.e. does it use more than one
core? and possibly, how, are there any documentation about this.
thomas
---(end of
On 9/5/07, Thomas Finneid [EMAIL PROTECTED] wrote:
how does pg utilise multi cpus/cores, i.e. does it use more than one
core? and possibly, how, are there any documentation about this.
Unlike other systems which manage their own affinity and
prioritization, Postgres relies solely on the OS to
On 2007-09-05 Scott Marlowe wrote:
On 9/5/07, Ansgar -59cobalt- Wiechers [EMAIL PROTECTED] wrote:
On 2007-09-05 Scott Marlowe wrote:
And there's the issue that with windows / NTFS that when one process
opens a file for read, it locks it for all other users. This means
that things like virus
On 9/5/07, Thomas Finneid [EMAIL PROTECTED] wrote:
how does pg utilise multi cpus/cores, i.e. does it use more than one
core? and possibly, how, are there any documentation about this.
PostgreSQL creates a new process to handle each connection to the
database. Multiple sessions can therefore
On 9/5/07, Ansgar -59cobalt- Wiechers [EMAIL PROTECTED] wrote:
On 2007-09-05 Scott Marlowe wrote:
On 9/5/07, Ansgar -59cobalt- Wiechers [EMAIL PROTECTED] wrote:
On 2007-09-05 Scott Marlowe wrote:
And there's the issue that with windows / NTFS that when one process
opens a file for read,
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Carlo Stonebanks wrote:
Unfortunately, LINUX is not an option at this time. We looked into it; there
is no *NIX expertise in the enterprise. However, I have raised this issue in
various forums before, and when pressed no one was willing to say that
35 matches
Mail list logo
