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 an
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 inde
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 order
"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
"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 ru
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 fi
>
> 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 : pgs
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 y
"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,
>
"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 r
"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 othe
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
> *DEFI
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
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 ref
>> 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 tha
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 performan
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
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 becau
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 elab
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 s
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 s
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 d
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
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, u
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 serio
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 rg
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 broadcast)--
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 t
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 thing
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 therefo
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
-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 th
On 2007-09-05 Scott Marlowe wrote:
> 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 / NT
Hi guys,
I'm have the rare opportunity to spec the hardware for a new database
server. It's going to replace an older one, driving a social networking
web application. The current server (a quad opteron with 4Gb of RAM and
80Gb fast SCSI RAID10) is coping with an average load of ranging between
1
37 matches
Mail list logo
