[PERFORM] Distant mirroring

Hello, I'm trying to install a solution to permit me to : - Secure the datas, without RAID - Giving ability to increase the potentiality of the database towards the needs. I have read about slony, DRBD, pgpool I don't find the good system to do what I want. I manage for now 50 millions o

Re: [PERFORM] Distant mirroring

On Sat, Aug 9, 2008 at 11:29 AM, dforum <[EMAIL PROTECTED]> wrote: > Hello, > > I'm trying to install a solution to permit me to : > - Secure the datas, without RAID Nothing beats a simple mirror set for simplicity while protecting the data, and for a pretty cheap cost. How much is your data wort

[PERFORM] why query plan for the inner SELECT of WHERE x IN is wrong, but when run the inner query alone is OK?

Something goes wrong that this query plan thinks there is only gonna be 1 row from (SELECT uid FROM alog ... ) so chooses such query plan, and thus it runs forever (at least so long that I didn't bother to wait, like 10 minutes): miernik=> EXPLAIN UPDATE cnts SET p0 = FALSE WHERE uid IN (SELECT u

Re: [PERFORM] why query plan for the inner SELECT of WHERE x IN is wrong, but when run the inner query alone is OK?

Miernik <[EMAIL PROTECTED]> writes: > miernik=> EXPLAIN UPDATE cnts SET p0 = FALSE WHERE uid IN (SELECT uid FROM > alog WHERE pid = 3452654 AND o = 1); > QUERY PLAN > -

Re: [PERFORM] why query plan for the inner SELECT of WHERE x IN is wrong, but when run the inner query alone is OK?

Tom Lane <[EMAIL PROTECTED]> wrote: > Miernik <[EMAIL PROTECTED]> writes: >> miernik=> EXPLAIN UPDATE cnts SET p0 = FALSE WHERE uid IN (SELECT uid FROM >> alog WHERE pid = 3452654 AND o = 1); >> QUERY PLAN >> ---

Re: [PERFORM] why query plan for the inner SELECT of WHERE x IN is wrong, but when run the inner query alone is OK?

Miernik <[EMAIL PROTECTED]> wrote: > How can I bring it back to working? Like un-run ANALYZE on that table or > something? All was running reasonably well before I changed from > autovacuum to running ANALYZE manually, and I thought I would improve > performance... ;( I now removed all manual ANAL

Re: [PERFORM] why query plan for the inner SELECT of WHERE x IN is wrong, but when run the inner query alone is OK?

Miernik <[EMAIL PROTECTED]> writes: > Tom Lane <[EMAIL PROTECTED]> wrote: >> If necessary, turn off enable_hashjoin and enable_mergejoin so we can >> see a comparable plan. > After doing that it thinks like this: > miernik=> explain select * from cnts, alog where alog.uid = cnts.uid; >

Re: [PERFORM] why query plan for the inner SELECT of WHERE x IN is wrong, but when run the inner query alone is OK?

On Sat, Aug 09, 2008 at 05:37:29PM -0400, Tom Lane wrote: > > miernik=> explain select * from cnts, alog where alog.uid = cnts.uid; > >QUERY PLAN > > - > > Nested Loop (

Re: [PERFORM] why query plan for the inner SELECT of WHERE x IN is wrong, but when run the inner query alone is OK?

Miernik <[EMAIL PROTECTED]> wrote: > Something goes wrong that this query plan thinks there is only gonna be > 1 row from (SELECT uid FROM alog ... ) so chooses such query plan, and > thus it runs forever (at least so long that I didn't bother to wait, > like 10 minutes): > > > miernik=> EXPLAIN

Re: [PERFORM] why query plan for the inner SELECT of WHERE x IN is wrong, but when run the inner query alone is OK?

Miernik <[EMAIL PROTECTED]> wrote: > I present a SELECT uid plan with the 1000 table also below, just to be > sure, this is the "bad" plan, that takes forever: > > miernik=> EXPLAIN SELECT uid FROM cnts WHERE uid IN (SELECT uid FROM alog > WHERE pid = 3452654 AND o = 1); >

Re: [PERFORM] why query plan for the inner SELECT of WHERE x IN is wrong, but when run the inner query alone is OK?

It is hardware dependent. The estimates are not time estimates, but on an arbitrary scale. On the server I work with, the estimates are almost always 10x larger than the run times, and sometimes more than 50x. (many GBs RAM, 8 CPU cores, more than 10 disks, standard optimizer settings other tha