Re: [PERFORM] optimize query with a maximum(date) extraction
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?
"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?
-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?
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?
"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?
"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?
-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?
-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?
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?
"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?
--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?
-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?
-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
