sympathy from anyone.
>
> In my case it is immutable. The username never changes.
>
Even if your data never changes it *can* change so the function should
be at most stable not immutable.
regards,
Jaime Casanova
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
our queries.
> Assuming each database is performing 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?
>
> Th
ow how many items it has?
>
> Thanks,
> Rodrigo
>
you 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 ta
informix potential...
maybe the same is in your case...
--
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
regards, tom lane
>
Maybe he is using some kind of weird ERP... take the case of BaaN
(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 ;)
15000 frequently accessed tables doesn't strike me
> > as being something that can possibly turn out well. You have, in
> > effect, more tables than (arguably) bloated ERP systems like SAP R/3;
> > it only has a few thousand tables, and since many are module-specific,
>
least. And I also tried this on a 2GB machine, and
> swapping was not a problem. If I used 10x more buffers, I would in
> essence remove the OS buffers.
>
How many disks do you have? (i wonder if you say 1)
- in most cases is good idea to have the WAL file in another disk...
What t
--
> Seq Scan on croute (cost=1.00..10780.64 rows=1030
> width=103) (actual time=29.626..29.879 rows=1 loops=1)
> Filter: (('193.68.0.8/32'::cidr <<= network) AND ((archived_at IS
> NULL) O
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
t; Total runtime: 25136.190 ms
> (8 rows)
>
show the tables and the indexes for those tables
> This is running on just about the world's slowest server (with a laptop hard
> drive to boot), but how can I avoid the seq scan, or in general speed up this
> query?
>
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,
gt;
using explicit INNER JOIN syntax and parenthesis
>
> 3. Don't flatten sub-selects
>
> select ... from (select ... from FOO where ...) as X where ...;
>
select ... from (select ... from FOO where ... offset 0) as X where ...;
> This would do the inner select then us
e right (they
> were set when hardware was very different then it is today) so some way to
> gather real-world stats and set the system defaults based on actual
> hardware performance is really the right way to go (even for things like
> sequential scan speed that are set in the config file today)
>
> David Lang
>
there was discussion on this and IIRC the consensus was that could be
useful tu give some statistics to user defined functions... i don't if
someone is working on this or even if it is doable...
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
operations get a "sorry, I couldn't find a good way to
> do that" message, rather than all the users find that their system has
> effectively stopped working.
>
> Kyle Cordes
> www.kylecordes.com
>
>
set statement_timeout in postgresql.conf
--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---(end of broadcast)---
TIP 6: explain analyze is your friend
afford to have the optimizer suddenly decide that
> some other 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: DataBa
he entire orders table for o.id_status > 3...
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
t; Package: postgresql-7.4
maybe, because you are in developing state, you can start to think in
upgrading to 8.1
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will
hing :)
and the advantages is that if your average transaction is more than
64kb large all wal data will be in memory until commit, actually i
thing it should be large enough to accomodate more than one
transaction but i'm not sure about that one... i usually use 1Mb for
OLTP systems
--
here 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
Certainly it'll 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
--
growing
> relatively to time and not the UPDATE time execution.
> I note that to stop the explosion of the Select time execution, i m using
> frequently the vaccum query on the cookies table.
> Set the autovacuum parmaeter in the configuation file to on wasn't able to
> rempla
out the
> biggest (in storage 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 ;)
--
, the Designs 1 and 2 would be
> essentially equivalent when it comes to vacuuming.
>
> Thanks,
> Craig
>
design 1 is normalized and better
design 2 is denormalized and a bad approach no matter the RDBMS
update does delete + insert, and vacuum is the way to recover the space
--
Atentamen
haven't 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 u
implicit transaction
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
"Programm
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?
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, pl
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?
___
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
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 pgs
What the statistics are? Where can i view it? where can i find info about
its field and why are they valuable information to performance?
thanx in advance,
Jaime Casanova
_
Protect your PC - get McAfee.com VirusScan Online
http
Thanks for the answer.
I know the question was to primitive (it claims: i have no idea about
databases).
But i simply didn't find the answer and if a don't ask i won't learn.
Someday i will talk with Tom Lane about how to improve the planner but until
that day comes i have a lot of technical th
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
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 Cas
> 'myval';
>
> 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
and are plenned different.
>
> Wh
--- 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
>
> > Why is it so completely off about the selectivity
> of the IS NULL clause?
null values are not indexable, is that your question?
If it is your question then create a partial index
with where deactive is null.
regards,
Jaime Casanova
_
> long search_paths, but I wouldn't be surprised if
> it's bad. But as long as you don't do that,
> I don't believe 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
t
rk test or script that
can be used?
3) any comments?
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
constitution
and cannot be denied or revoked, IANAL.
And like stated by Mitch just numbers 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
--
than a index scan? i 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)-
02 and it thinks 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 settings
ueries run faster using EXISTS instead of large IN
clauses... actually, it was NOT EXISTS replacing a NOT IN
while i'm not telling EXISTS is better i actually know in some cases is better
--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Guayaquil - Ecuador
Cel. (593) 87171
r document from the "technical documentation" 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 journal
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
On Wed, Dec 17, 2008 at 2:34 AM, Albe Laurenz 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
lain analyze says it will execute in 175.952ms and
because 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
On Wed, Dec 17, 2008 at 9:18 AM, Robert Haas wrote:
> On Tue, Dec 16, 2008 at 2:32 PM, 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 f
many transactions are
we processing per period of time?
--
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 you
S: i have max_connections to 1024, 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 sist
#x27;Habilitado')
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
On Thu, Aug 20, 2009 at 11:38 PM, Jeff Janes wrote:
>> -- Forwarded message --
>> From: Jaime Casanova
>> To: psql performance list
>> Date: Wed, 19 Aug 2009 19:25:11 -0500
>> Subject: [PERFORMANCE] how to set wal_buffers
>> Hi,
>>
>&g
d under one of the stat tables.
>
+1, at least could be useful for some of us that do not have dtrace
--
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-p
e STATEMENT lines 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
On Mon, Jan 11, 2010 at 2:07 PM, Robert Haas wrote:
> On Mon, Jan 11, 2010 at 1:15 PM, Jaime Casanova
> wrote:
>> On Sun, Sep 13, 2009 at 5:37 PM, Tom Lane wrote:
>>>
>>> It might be useful to turn on trace_sort to see if the small files
>>> are coming
On Mon, Jan 11, 2010 at 2:14 PM, Jaime Casanova
wrote:
> On Mon, Jan 11, 2010 at 2:07 PM, Robert Haas wrote:
>> On Mon, Jan 11, 2010 at 1:15 PM, Jaime Casanova
>> wrote:
>>> On Sun, Sep 13, 2009 at 5:37 PM, Tom Lane wrote:
>>>>
>>>> It might be
On Mon, Jan 11, 2010 at 3:18 PM, Tom Lane wrote:
> Jaime Casanova 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
hen i removed
#ifdef HJDEBUG it says that in total i was using 10 batchs but there
were 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,
Jai
On Wed, Jan 13, 2010 at 11:11 AM, Robert Haas wrote:
>
> Well, what about when we're just doing EXPLAIN, not EXPLAIN ANALYZE?
> It'll add another line to the output for the expected number of
> batches.
>
and when we are in EXPLAIN ANALYZE the real number as well
't get
reformatted 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-performan
nel... it will cause more
problems than the one it solves.
what you can do is:
1) execute:
SET enable_hashjoin TO OFF;
SELECT here
RESET enable_hashjoin TO ON;
2) in a function:
CREATE FUNCTION do_something() RETURNS bigint AS
$$
SELECT here
$$ LANGUAGE sql SET enable_hashjoin TO OFF STABLE;
61 matches
Mail list logo
