Re: [PERFORM] Very slow inner join query Unacceptable latency.

TO OFF STABLE; -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación Phone: +593 4 5107566 Cell: +593 987171157 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http

Re: [PERFORM] Very slow inner join query Unacceptable latency.

by the mail client. what version of postgres is this? -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación Phone: +593 4 5107566 Cell: +593 987171157 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes

Re: [PERFORM] PostgreSQL as a local in-memory cache

all be significant to the performance focus. why is that? isn't simply execute pg_resetxlog enough? specially 'cause OP doesn't care about loosing some transactions -- Jaime Casanova www.2ndQuadrant.com Soporte y capacitación de PostgreSQL -- Sent via pgsql-performance mailing list

Re: [PERFORM] How to fast the REINDEX

? if not, there is nothing to do... REINDEX is not a mantenance task on postgres -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes

Re: [PERFORM] Why Wal_buffer is 64KB

, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] [PERFORMANCE] work_mem vs temp files issue

? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] [PERFORMANCE] work_mem vs temp files issue

14 temp files created (i guess we use 1 file per batch, no?) nbatch = 1, nbuckets = 1024 nbatch = 1, nbuckets = 1024 nbatch = 8, nbuckets = 2048 -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent

Re: [PERFORM] [PERFORMANCE] work_mem vs temp files issue

because they were redundant), seems like all the temp files are used to execute the same sentence... BTW, this is my laptop no the server. -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql

Re: [PERFORM] [PERFORMANCE] work_mem vs temp files issue

On Mon, Jan 11, 2010 at 2:07 PM, Robert Haas robertmh...@gmail.com wrote: On Mon, Jan 11, 2010 at 1:15 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: On Sun, Sep 13, 2009 at 5:37 PM, Tom Lane t...@sss.pgh.pa.us wrote: It might be useful to turn on trace_sort to see if the small files

Re: [PERFORM] [PERFORMANCE] work_mem vs temp files issue

On Mon, Jan 11, 2010 at 2:14 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: On Mon, Jan 11, 2010 at 2:07 PM, Robert Haas robertmh...@gmail.com wrote: On Mon, Jan 11, 2010 at 1:15 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: On Sun, Sep 13, 2009 at 5:37 PM, Tom Lane t

Re: [PERFORM] [PERFORMANCE] work_mem vs temp files issue

On Mon, Jan 11, 2010 at 3:18 PM, Tom Lane t...@sss.pgh.pa.us wrote: Jaime Casanova jcasa...@systemguards.com.ec writes: LOG:  begin tuple sort: nkeys = 1, workMem = 1024, randomAccess = f LOG:  switching to bounded heapsort at 641 tuples: CPU 0.08s/0.13u sec elapsed 0.25 sec LOG:  temporary

Re: [PERFORM] [PERFORMANCE] how to set wal_buffers

, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] [PERFORMANCE] how to set wal_buffers

On Thu, Aug 20, 2009 at 11:38 PM, Jeff Janesjeff.ja...@gmail.com wrote: -- Forwarded message -- From: Jaime Casanova jcasa...@systemguards.com.ec To: psql performance list pgsql-performance@postgresql.org Date: Wed, 19 Aug 2009 19:25:11 -0500 Subject: [PERFORMANCE] how to set

[PERFORM] limiting results makes the query slower

') then 'Habilitado' else 'Deshabilitado' end as empresa_id from tgen_persona Per, tcom_invitacion Inv where Per.persona_id = Inv.persona_id and inv.id_soli_compra = '60505' ORDER BY Per.razon_social asc limit 20 offset 0 -- Atentamente, Jaime Casanova Soporte

[PERFORM] [PERFORMANCE] work_mem vs temp files issue

, i know i need a pool but the app is still opening persistent conecctions to the db, so is not like i could raise work_mem just easy until the app gets fixed -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157

Re: [PERFORM] measure database contention

On Wed, Dec 17, 2008 at 2:34 AM, Albe Laurenz laurenz.a...@wien.gv.at wrote: Jaime Casanova wrote: we have a some bad queries (developers are working on that), some of them run in 17 secs and that is the average but when analyzing logs i found that from time to time some of them took upto 3

Re: [PERFORM] measure database contention

of network transfer of data executing this from pgadmin in another machine it runs for 17s... but from time to time pgFouine is shown upto 345.11 sec -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via

Re: [PERFORM] measure database contention

On Wed, Dec 17, 2008 at 9:18 AM, Robert Haas robertmh...@gmail.com wrote: On Tue, Dec 16, 2008 at 2:32 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: we have a some bad queries (developers are working on that), some of them run in 17 secs and that is the average but when analyzing logs

[PERFORM] measure database contention

for contention problems? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org

[PERFORM] filesystem options for WAL

that for partitions where you have the tables and indexes is better to have journaling and for partitions for the WAL is better to not have journalling... i tought it has to be the other way (tables indices without journalling, WAL with journalling) -- regards, Jaime Casanova Soporte y

Re: [PERFORM] Savepoint performance

unless you put BEGIN/COMMIT between a block of statements... in that case if an error occurs the entire block of statements must ROLLBACK... if other db's doesn't do that, is a bug in their implementation of the SQL standard -- regards, Jaime Casanova Programming today is a race between software

Re: [PERFORM] FWD: Update touches unrelated indexes?

committed yet we still want the old version (old index entry)... -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning

Re: [PERFORM] update == delete + insert?

, and vacuum is the way to recover the space -- Atentamente, Jaime Casanova What they (MySQL) lose in usability, they gain back in benchmarks, and that's all that matters: getting the wrong answer really fast. Randal L. Schwartz ---(end

Re: [PERFORM] big databases hospitals

space, in record number, in field number, and maybe table number) postgresql databases. here you can see some limits of postgresql: http://www.postgresql.org/about/ -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast

Re: [PERFORM] Please Help: PostgreSQL performance Optimization

don't know if this behaivour is normal? Thanks, Jamal please execute EXPLAIN ANALYZE your query and show the results, is the only way to know what's happening -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast

Re: [PERFORM] 500x speed-down: Wrong query plan?

in upgrading to 8.1 -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(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

Re: [PERFORM] Improving Inner Join Performance

, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [PERFORM] Overriding the optimizer

plan might be faster when in fact it's much slower. Plan stability doesn't mean time response stability... The plan that today is almost instantaneous tomorrow can take hours... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast

Re: [PERFORM] Overriding the optimizer

-- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [PERFORM] Overriding the optimizer

On 12/15/05, Christopher Kings-Lynne [EMAIL PROTECTED] wrote: select * from my_table where row_num = 5 and row_num 10 and myfunc(foo, bar); You just create an index on myfunc(foo, bar) Chris only if myfunc(foo, bar) is immutable... -- regards, Jaime Casanova (DBA

Re: [PERFORM] Overriding the optimizer

FOO where ... offset 0) as X where ...; This would do the inner select then use the result in the outer select, and wouldn't attempt to flatten the query. Thanks, Craig what else? -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast

Re: [PERFORM] Simple Join

to boot), but how can I avoid the seq scan, or in general speed up this query? to_ship will have far less tuples than ordered_products, but it's still not small, as you can see. -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast

Re: [PERFORM] Executing a shell command from a PG function

On 12/10/05, Yves Vindevogel [EMAIL PROTECTED] wrote: Hi, Is it possible to run a shell script, passing values of fields to it, in a Postgres function ? Yves Vindevogel search for the pl/sh language -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end

Re: [PERFORM] Query not using index

; although i think that your query can make use of bitmap index in 8.1 -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] 15,000 tables

... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(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

Re: [PERFORM] 15,000 tables

(sadly i use it in my work): BaaN creates about 1200 tables per company and i have no control of it... we have about 12000 tables right now... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 9: In versions below

Re: [PERFORM] 15,000 tables

only a few hundred that are hot spots. No 15000 there.. I think that my systems confirms with the 80/20 rule ... . How many disks do you have i imagine you can put tables forming one logical database in a tablespace and have tables spread on various disks... -- Atentamente, Jaime Casanova

Re: [PERFORM] Newbie question: ultra fast count(*)

really *need* this? you can do SELECT reltuples FROM pg_class WHERE relname = 'your_table_name'; but this will give you an estimate... if you want real values you can make a TRIGGER that maintain a counter in another table -- regards, Jaime Casanova (DBA: DataBase Aniquilator

Re: [PERFORM] What is the max number of database I can create in an instance of pgsql?

well alone, how would putting 10-20 of them together in one instance affect postgres? In terms of getting a new server for this project, how do I gauge how powerful of a server should I get? Thanks. -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end

Re: [PERFORM] immutable functions vs. join for lookups ?

so the function should be at most stable not immutable. regards, Jaime Casanova ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

[PERFORM] estimated rows vs. actual rows

notice the diff between the estimated rows and actual rows (almost 2000). Can this affect the query plan? i think this is a problem of statistics, am i right? if so, what can be done? regards, Jaime Casanova ---(end of broadcast)--- TIP 3: if posting

Re: [PERFORM] estimated rows vs. actual rows

will retrieve 2610 (almost 1.81% of the total). it won't be faster with an index? i know, i will suggest him to probe to be sure. just an opinion. regards, Jaime Casanova ---(end of broadcast)--- TIP 7: don't forget to increase your free space map

Re: [PERFORM] Benchmark

are not lies that can be pursued in a court of law. Think anout it, In USA you can speak and publish about the President but cannot say anything about M$ or Oracles' DBMS? regards, Jaime Casanova ---(end of broadcast)--- TIP 7: don't forget to increase your

Re: [PERFORM] [PERFORMANCE] Big number of schemas (3500) into a single database

there will be any problems. if i do a select with fully qualified table names it will search in the search_path or it will go directly to the schema? Just for know. regards, Jaime Casanova _ Do You Yahoo!? Información de Estados Unidos y

Re: [PERFORM] Slow execution time when querying view with WHERE clause

a partial index with where deactive is null. regards, Jaime Casanova _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com ---(end of broadcast

Re: [PERFORM] Sequential Scan with LIMIT

--- John Meinel [EMAIL PROTECTED] escribió: Jaime Casanova wrote: [...] I'm not sure. They all return the same information. of course, both queries will return the same but that's just because you forced it. LIMIT and DISTINCT are different things so they behave

Re: [PERFORM] Sequential Scan with LIMIT

such a thing? regards, Jaime Casanova _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com ---(end of broadcast

Re: [PERFORM] [HACKERS] Wrong index choosen?

Hi all, just as a question. There will be some day a feature that let you force the planner to use an specific index, like oracle does? Of course the planner is smart enough most times but sometimes such an option would be usefull, don't you think so? Thanx in advance, Jaime Casanova

[PERFORM] numeric data types

Hi all, i have a question, is there any advantages in using numeric(1) or numeric(2) in place of smallint? is there any diff. in performance if i use smallint in place of integer? Thanx in advance, Jaime Casanova _ Help STOP SPAM

Re: [PERFORM] select slow?

On Tuesday 30 March 2004 20:25, Jaime Casanova wrote: hi all, i have an amd athlon with 256 ram (i know, this is not a *real* server but my tables are small) Nothing wrong with it - it's what I still use as my development server. i'm using vb6 (win98) with pgsql-7.3.4 (rh8) trough

[PERFORM] select slow?

hi all, i have an amd athlon with 256 ram (i know, this is not a *real* server but my tables are small) i'm using vb6 (win98) with pgsql-7.3.4 (rh8) trough the psqlodbc. when i do a select in took long to execute, here is an example table icc_m_banco CREATE TABLE ICC_M_BANCO ( CodBanco

Re: [PERFORM] column size too large, is this a bug?

Andrew, I used to use the connect-by patch, but have since rewritten everything to use a nested set model. Cool! You're probably the only person I know other than me using nested sets in a production environment. can you explain me what is a nested set?

Re: [PERFORM] column size too large, is this a bug?

thanx a lot _ STOP MORE SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail ---(end of broadcast)--- TIP 3: if posting/reading through Usenet,

Re: [PERFORM] [ADMIN] Raw vs Filesystem

ok. if i don't misunderstand you (english is not my mother tongue, so i can be wrong). your point is that speed is not necesarily performance, that's right. so, the real question is what is the best filesystem for optimal speed in postgresql?