Re: [PERFORM] optimize query with a maximum(date) extraction

2007-09-08 Thread Dimitri
BTW, will it improve something if you change your index to "my_table(
id, the_date )"?

Rgds,
-Dimitri

On 9/5/07, JS Ubei <[EMAIL PROTECTED]> wrote:
> 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 index with an aggregative function.
>
> How I can do ?
>
> thanks,
>
> jsubei
>
>
>
>
>
> _
> Ne gardez plus qu'une seule adresse mail ! Copiez vos mails vers Yahoo! Mail
>
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
>

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [PERFORM] DRBD and Postgres: how to improve the perfomance?

2007-09-08 Thread Gregory Stark
"Simon Riggs" <[EMAIL PROTECTED]> writes:

> You're right, but the distinction is a small one. What are the chances
> of losing two independent servers within a few milliseconds of each
> other? 

If they're on the same power bus?

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com

---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [PERFORM] DRBD and Postgres: how to improve the perfomance?

2007-09-08 Thread Joshua D. Drake
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Gregory Stark wrote:
> "Simon Riggs" <[EMAIL PROTECTED]> writes:
> 
>> You're right, but the distinction is a small one. What are the chances
>> of losing two independent servers within a few milliseconds of each
>> other? 
> 
> If they're on the same power bus?

That chance is minuscule or at least should be. Of course we are
assuming some level of conditioned power that is independent of the
power bus, e.g; a UPS.

Joshua D. Drake





- --

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564   24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997  http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFG4sqvATb/zqfZUUQRAq/qAKCkkFX/hTddRJriMGMYhjy04REwvgCfUoY5
pzcyvahVvsaAL8qlkJVtbX0=
=nzIH
-END PGP SIGNATURE-

---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [PERFORM] DRBD and Postgres: how to improve the perfomance?

2007-09-08 Thread Stefan Kaltenbrunner
Joshua D. Drake wrote:
> Gregory Stark wrote:
>> "Simon Riggs" <[EMAIL PROTECTED]> writes:
> 
>>> You're right, but the distinction is a small one. What are the chances
>>> of losing two independent servers within a few milliseconds of each
>>> other? 
>> If they're on the same power bus?
> 
> That chance is minuscule or at least should be. Of course we are
> assuming some level of conditioned power that is independent of the
> power bus, e.g; a UPS.

how is that making it different in practise ? - if both are on the same
UPS they are affectively on the same power bus ...
If the UPS fails (or the generator is not kicking in which happens way
more often than people would believe) they could still fail at the very
same time 


Stefan

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org


Re: [PERFORM] DRBD and Postgres: how to improve the perfomance?

2007-09-08 Thread Tom Lane
"Joshua D. Drake" <[EMAIL PROTECTED]> writes:
> Gregory Stark wrote:
>> "Simon Riggs" <[EMAIL PROTECTED]> writes:
>>> You're right, but the distinction is a small one. What are the chances
>>> of losing two independent servers within a few milliseconds of each
>>> other? 
>> 
>> If they're on the same power bus?

> That chance is minuscule or at least should be.

It seems a bit silly to be doing replication to a slave server that has
any common point of failure with the master.

However, it seems like the point here is not so much "can you recover
your data" as what a commit means.  Do you want a commit reported to the
client to mean the data is safely down to disk in both places, or only
one?

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [PERFORM] DRBD and Postgres: how to improve the perfomance?

2007-09-08 Thread Gregory Stark
"Joshua D. Drake" <[EMAIL PROTECTED]> writes:

> That chance is minuscule or at least should be. Of course we are
> assuming some level of conditioned power that is independent of the
> power bus, e.g; a UPS.

I find your faith in UPSes charmingly quaint.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match


Re: [PERFORM] DRBD and Postgres: how to improve the perfomance?

2007-09-08 Thread Joshua D. Drake
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Stefan Kaltenbrunner wrote:
> Joshua D. Drake wrote:
>> Gregory Stark wrote:
>>> "Simon Riggs" <[EMAIL PROTECTED]> writes:
 You're right, but the distinction is a small one. What are the chances
 of losing two independent servers within a few milliseconds of each
 other? 
>>> If they're on the same power bus?
>> That chance is minuscule or at least should be. Of course we are
>> assuming some level of conditioned power that is independent of the
>> power bus, e.g; a UPS.
> 
> how is that making it different in practise ? - if both are on the same
> UPS they are affectively on the same power bus ...

Well I was thinking the bus that is in the wall. I would assume that
people were smart enough to have independent UPS systems for each server.

city power->line conditioning generator->panel->plug->UPS->server

wash, rinse repeat.

> If the UPS fails (or the generator is not kicking in which happens way
> more often than people would believe) they could still fail at the very
> same time 

Sincerely,

Joshua D. Drake





> 
> 
> Stefan
> 
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
> 
>http://archives.postgresql.org
> 


- --

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564   24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997  http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFG4tOKATb/zqfZUUQRAiSTAJ4pqQqsP7aH9GPJYjY3hZDvKzU8cACeKKJ3
wAae0tl2XswsjgEncIsOBlw=
=xsGZ
-END PGP SIGNATURE-

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org


Re: [PERFORM] DRBD and Postgres: how to improve the perfomance?

2007-09-08 Thread Joshua D. Drake
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Gregory Stark wrote:
> "Joshua D. Drake" <[EMAIL PROTECTED]> writes:
> 
>> That chance is minuscule or at least should be. Of course we are
>> assuming some level of conditioned power that is independent of the
>> power bus, e.g; a UPS.
> 
> I find your faith in UPSes charmingly quaint.

It isn't my faith in a UPS. It is my real world knowledge.

Further I will exert what I already replied to Stefan:

city power->line conditioning generator->panel->plug->UPS->server

You would have to have lightning handed by God to your server to have a
total power failure without proper shutdown in the above scenario.

Sincerely,

Joshua D. Drake








- --

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564   24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997  http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFG4tWpATb/zqfZUUQRAl00AJ4jC/CWkqrxeUjT0REedQAG3cvPPgCcCKkU
zbCu41UT25PnL7f7bT7dfXQ=
=tV5r
-END PGP SIGNATURE-

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq


Re: [PERFORM] DRBD and Postgres: how to improve the perfomance?

2007-09-08 Thread Stefan Kaltenbrunner
Joshua D. Drake wrote:
> Stefan Kaltenbrunner wrote:
>> Joshua D. Drake wrote:
>>> Gregory Stark wrote:
 "Simon Riggs" <[EMAIL PROTECTED]> writes:
> You're right, but the distinction is a small one. What are the chances
> of losing two independent servers within a few milliseconds of each
> other? 
 If they're on the same power bus?
>>> That chance is minuscule or at least should be. Of course we are
>>> assuming some level of conditioned power that is independent of the
>>> power bus, e.g; a UPS.
>> how is that making it different in practise ? - if both are on the same
>> UPS they are affectively on the same power bus ...
> 
> Well I was thinking the bus that is in the wall. I would assume that
> people were smart enough to have independent UPS systems for each server.
> 
> city power->line conditioning generator->panel->plug->UPS->server
> 
> wash, rinse repeat.

the typical datacenter version of this is actually more like:

city power->UPS (with generator in parallel)->panel->plug

or

city power->flywheel->(maybe UPS)->panel->plug

it is not really that common to have say two different UPS feeds in your
rack (at least not for normal housing or the average corporate
datacenter) - mostly you get two feeds from different power distribution
panels (so different breakers) but that's about it.
Having a local UPS attached is usually not really that helpful either
because those have limited capacity need space and are an additional
thing that can (and will) fail.


Stefan

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq


Re: [PERFORM] DRBD and Postgres: how to improve the perfomance?

2007-09-08 Thread Gregory Stark
"Joshua D. Drake" <[EMAIL PROTECTED]> writes:

> It isn't my faith in a UPS. It is my real world knowledge.
>
> Further I will exert what I already replied to Stefan:
>
> city power->line conditioning generator->panel->plug->UPS->server
>
> You would have to have lightning handed by God to your server to have a
> total power failure without proper shutdown in the above scenario.

Which happens a couple times a year to various trusting souls. I suppose
you're not a regular reader of Risks? Or a regular user of Livejournal for
that matter?

Analog is hard, let's go shopping.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [PERFORM] DRBD and Postgres: how to improve the perfomance?

2007-09-08 Thread Bernd Helmle
--On Samstag, September 08, 2007 12:39:37 -0400 Tom Lane 
<[EMAIL PROTECTED]> wrote:



However, it seems like the point here is not so much "can you recover
your data" as what a commit means.  Do you want a commit reported to the
client to mean the data is safely down to disk in both places, or only
one?


Yeah, that's what i meant to say. DRBD provides a handful other tweaks 
besides changing the sync protocol, i'd start with them first. You can get 
back experimenting with the sync protocol if there are still performance 
issues then. I don't hesitate changing to B as long as I'm aware that it 
changed semantics and I can deal with them.


--
 Thanks

   Bernd

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org


Re: [PERFORM] DRBD and Postgres: how to improve the perfomance?

2007-09-08 Thread Joshua D. Drake
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Stefan Kaltenbrunner wrote:
> Joshua D. Drake wrote:
>> Stefan Kaltenbrunner wrote:

>>> how is that making it different in practise ? - if both are on the same
>>> UPS they are affectively on the same power bus ...
>> Well I was thinking the bus that is in the wall. I would assume that
>> people were smart enough to have independent UPS systems for each server.
>>
>> city power->line conditioning generator->panel->plug->UPS->server
>>
>> wash, rinse repeat.
> 
> the typical datacenter version of this is actually more like:
> 
> city power->UPS (with generator in parallel)->panel->plug

Right, this is what we have at our colo except that I add a UPS where
appropriate in between panel and plug.

> city power->flywheel->(maybe UPS)->panel->plug
> 
> it is not really that common to have say two different UPS feeds in your
> rack (at least not for normal housing or the average corporate
> datacenter) - mostly you get two feeds from different power distribution
> panels (so different breakers) but that's about it.

> Having a local UPS attached is usually not really that helpful either
> because those have limited capacity need space and are an additional
> thing that can (and will) fail.

We don't have the capacity issue. We use the UPS explicitly for specific
cases (like the one mentioned at the beginning of the thread). The whole
idea is to insure clean shutdown in case of "total" power failure.


Sincerely,

Joshua D. Drake
> 
> 
> Stefan
> 


- --

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564   24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997  http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFG4xM5ATb/zqfZUUQRAszhAJ4qmwJQFHd/O5/alOSg1exrYEDe0wCeN6na
8BgWWO1aGELPOuX3xivEBVU=
=ETwV
-END PGP SIGNATURE-

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly


Re: [PERFORM] DRBD and Postgres: how to improve the perfomance?

2007-09-08 Thread Joshua D. Drake
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Joshua D. Drake wrote:
> Stefan Kaltenbrunner wrote:
>> Joshua D. Drake wrote:
>>> Stefan Kaltenbrunner wrote:
> 
 how is that making it different in practise ? - if both are on the same
 UPS they are affectively on the same power bus ...
>>> Well I was thinking the bus that is in the wall. I would assume that
>>> people were smart enough to have independent UPS systems for each server.
>>>
>>> city power->line conditioning generator->panel->plug->UPS->server
>>>
>>> wash, rinse repeat.
>> the typical datacenter version of this is actually more like:
> 
>> city power->UPS (with generator in parallel)->panel->plug
> 
> Right, this is what we have at our colo except that I add a UPS where
> appropriate in between panel and plug.

Err plug and machine.

Joshua D. Drake
- ---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly



- --

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564   24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997  http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFG4xSTATb/zqfZUUQRAkgyAJwKLLz0Jywex/b3d6hk8L2gHsZaXQCfYCyH
6Z/mdtOvnXc4MixgxchrxNY=
=kv8v
-END PGP SIGNATURE-

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq