Harald Armin Massa wrote:
Yeah, I know the trial and error method. But I also learned that
reading the manuals and documentation often helps.
So after fastreading the various PostgreSQL tuning materials, I came
accross formulas to calculate a fine starting point for shared memory
size; and the
On Wed, 2006-10-18 at 17:35 -0500, Jim C. Nasby wrote:
> On Wed, Oct 18, 2006 at 03:32:15PM -0700, Jeff Davis wrote:
> > On Wed, 2006-10-18 at 17:10 -0500, Jim C. Nasby wrote:
> > > Sorry, don't have the earlier part of this thread, but what about...
> > >
> > > SELECT greatest(max(a), max(b)) ...
Jeff Davis <[EMAIL PROTECTED]> writes:
> If PostgreSQL could sort the result of a union by merging the results of
> two index scans, I think the problem would be solved. Is there something
> preventing this, or is it just something that needs to be added to the
> planner?
It's something on the wis
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> On Wed, Oct 18, 2006 at 03:20:19PM -0700, Graham Davis wrote:
>> When I run the same command to find the size after the VACUUM, it hasn't
>> changed.
> That's not really a useful test to see if VACUUM is working. VACUUM can
> only trim space off the en
On Wed, Oct 18, 2006 at 03:39:56PM -0700, Graham Davis wrote:
> So I guess any changes that were made to make VACUUM and FSM include
> indexes
> does not remove the necessity to reindex (as long as we don't want index
> sizes to bloat and grow larger than they need be).
> Is that correct?
Not in
So I guess any changes that were made to make VACUUM and FSM include
indexes
does not remove the necessity to reindex (as long as we don't want index
sizes to bloat and grow larger than they need be).
Is that correct?
Graham.
Jim C. Nasby wrote:
On Wed, Oct 18, 2006 at 03:20:19PM -0700, Gra
On Wed, Oct 18, 2006 at 03:32:15PM -0700, Jeff Davis wrote:
> On Wed, 2006-10-18 at 17:10 -0500, Jim C. Nasby wrote:
> > Sorry, don't have the earlier part of this thread, but what about...
> >
> > SELECT greatest(max(a), max(b)) ...
> >
> > ?
>
> To fill you in, we're trying to get the max of a
On Wed, Oct 18, 2006 at 03:20:19PM -0700, Graham Davis wrote:
> I have a question about index growth.
>
> The way I understand it, dead tuples in indexes were not reclaimed by
> VACUUM commands in the past. However, I've read in a few forum posts
> that this was changed somewhere between 7.4 an
On Wed, 2006-10-18 at 17:10 -0500, Jim C. Nasby wrote:
> Sorry, don't have the earlier part of this thread, but what about...
>
> SELECT greatest(max(a), max(b)) ...
>
> ?
To fill you in, we're trying to get the max of a union (a view across
two physical tables).
It can be done if you're creati
I have a question about index growth.
The way I understand it, dead tuples in indexes were not reclaimed by
VACUUM commands in the past. However, I've read in a few forum posts
that this was changed somewhere between 7.4 and 8.0.
I'm having an issue where my GIST indexes are growing quite la
On Wed, Oct 18, 2006 at 02:33:49PM -0700, Jeff Davis wrote:
> On Wed, 2006-10-18 at 23:19 +0200, Dimitri Fontaine wrote:
> > Le mercredi 18 octobre 2006 23:02, Ioana Danes a ??crit :
> > > I tried the partitioning scenario but I've got into
> > > the same problem. The max function is not using the
On Wed, 2006-10-18 at 23:19 +0200, Dimitri Fontaine wrote:
> Le mercredi 18 octobre 2006 23:02, Ioana Danes a écrit :
> > I tried the partitioning scenario but I've got into
> > the same problem. The max function is not using the
> > indexes on the two partitioned tables...
> >
> > Any other though
Le mercredi 18 octobre 2006 23:02, Ioana Danes a écrit :
> I tried the partitioning scenario but I've got into
> the same problem. The max function is not using the
> indexes on the two partitioned tables...
>
> Any other thoughts?
Did you make sure your test included table inheritance?
I'm not su
And PLEASE do not post something to 3 lists; it's a lot of extra traffic
for no reason.
Moving to -hackers.
On Wed, Oct 18, 2006 at 05:15:13PM -0400, jungmin shin wrote:
> Hello all,
>
> I read a paper, which is Query optimization in the presence of Foreign
> Functions.
> And the paper , there
On Wed, Oct 18, 2006 at 05:15:13PM -0400, jungmin shin wrote:
> Hello all,
>
> I read a paper, which is Query optimization in the presence of Foreign
> Functions.
> And the paper , there is a paragraph like below.
>
> In order to reduce the number of invocations, caching the results of
> invoca
Hello all,
I read a paper, which is Query optimization in the presence of Foreign Functions.
And the paper , there is a paragraph like below.
In order to reduce the number of invocations, caching the results of invocation was suggested in Postgres.
I'd like to know in detail about how postg
On Wed, 2006-10-18 at 15:51 -0400, Ioana Danes wrote:
> Hi everyone,
> Testing some selects I know we have in the application
> I got into a scenario where my plan does not work
> without doing code change. This scenario is:
>
> select max(transid) from alltransaction;
>
> because the planner doe
Hello,
I tried the partitioning scenario but I've got into
the same problem. The max function is not using the
indexes on the two partitioned tables...
Any other thoughts?
--- Ioana Danes <[EMAIL PROTECTED]> wrote:
> Thanks a lot I will give it a try.
>
> --- Dimitri Fontaine <[EMAIL PROTECTE
Jim C. Nasby wrote:
> On Sun, Oct 15, 2006 at 04:52:12PM +0200, Tobias Brox wrote:
>> Are there any logs that can help me, and eventually, are there any
>> ready-made scripts for checking when autovacuum is running, and
>> eventually for how long it keeps its transactions? I'll probably
>> write u
Thanks a lot I will give it a try.
--- Dimitri Fontaine <[EMAIL PROTECTED]> wrote:
> Hi,
>
> Le mercredi 18 octobre 2006 21:51, Ioana Danes a
> écrit :
> > I am doing a test for a scenario where I have 2
> > schemas one (public) for the operational data and
> > another one (archive) for old, a
On 10/18/06, Ioana Danes <[EMAIL PROTECTED]> wrote:
# explain select max(transid) from public.transaction; QUERYPLAN
-- Result (cost=0.04..0.05 rows=1 wi
Hi,
Le mercredi 18 octobre 2006 21:51, Ioana Danes a écrit :
> I am doing a test for a scenario where I have 2
> schemas one (public) for the operational data and
> another one (archive) for old, archived data. So
> basically I want to split the data from some huge
> tables in two. All data befor
Hi everyone,
I am doing a test for a scenario where I have 2
schemas one (public) for the operational data and
another one (archive) for old, archived data. So
basically I want to split the data from some huge
tables in two. All data before 2006 in archive and all
data after and including 2006 i
On 10/18/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote:
On Tue, Oct 17, 2006 at 12:51:19PM -0400, Merlin Moncure wrote:
> so, imo alexander is correct:
> contacto varchar(255)
>
> ...is a false constraint, why exactly 255? is that were the dart landed?
BTW, if we get variable-length varlena headers
On Tue, Oct 17, 2006 at 12:25:39PM +0200, Ruben Rubio wrote:
> First of all I have to say that I now the database is not ok. There was
> a people before me that didn't do the thinks right. I would like to
> normalize the database, but it takes too much time (there is is hundred
> of SQLs to change
On Tue, Oct 17, 2006 at 12:51:19PM -0400, Merlin Moncure wrote:
> so, imo alexander is correct:
> contacto varchar(255)
>
> ...is a false constraint, why exactly 255? is that were the dart landed?
BTW, if we get variable-length varlena headers at some point, then
setting certain limits might make
On Sun, Oct 15, 2006 at 04:52:12PM +0200, Tobias Brox wrote:
> Are there any logs that can help me, and eventually, are there any
> ready-made scripts for checking when autovacuum is running, and
> eventually for how long it keeps its transactions? I'll probably write
> up something myself if not.
On Mon, Oct 16, 2006 at 05:56:54PM -0400, Carlo Stonebanks wrote:
> >I think there's 2 things that would help this case. First, partition on
> > country. You can either do this on a table level or on an index level
> > by putting where clauses on the indexes (index method would be the
> > fastest o
Tom Lane a écrit :
Arnaud Lesauvage <[EMAIL PROTECTED]> writes:
When I join these two tables, the 2-column index of the first table is
not used.
Why does the query planner think that this plan is better ?
Hm, is gid by itself nearly unique in these tables? If so, the merge
join would get on
Arnaud Lesauvage <[EMAIL PROTECTED]> writes:
> When I join these two tables, the 2-column index of the first table is
> not used.
> Why does the query planner think that this plan is better ?
Hm, is gid by itself nearly unique in these tables? If so, the merge
join would get only marginally mor
On Tuesday 17 October 2006 22:55, Tom Lane wrote:
> Robert Treat <[EMAIL PROTECTED]> writes:
> > When it happens it tends to look something like this:
> > http://archives.postgresql.org/pgsql-performance/2006-01/msg00154.php
> >
> > Funny that for all the people who claim that improving the planner
Atesz <[EMAIL PROTECTED]> writes:
> My question: Why need this strict locking?
> In my opinion there isn't exclusion between the DROP CONSTRAINT and the
> SELECT.
This isn't going to be changed, because the likely direction of future
development is that the planner will start making use of const
On Wed, Oct 18, 2006 at 11:31:44AM +0200, Mario Weilguni wrote:
> It's not a bad idea. Usually I use postal codes with 25 chars, and never had
> any problem. With text, the limit would be ~1 GB. No matter how much testing
> in the application happens, the varchar(25) as last resort is a good idea
On Wed, 2006-10-18 at 09:24, Atesz wrote:
> Hi!
>
> I have a problem with ACCESS EXCLUSIVE lock when I drop a reference in
> transaction. I have 2 tables:
> create table a(id SERIAL primary key);
> create table b(id SERIAL primary key references a(id));
>
> After that I have 2 processes: P1,
Hi Merlin
I have disabled seq-scan and now it works like a charm. Thanks it was a saver.
Regards
Rohit
On 10/18/06, Bucky Jordan <[EMAIL PROTECTED]> wrote:
> > On 10/17/06, Rohit_Behl <[EMAIL PROTECTED]> wrote:
> > > Select events.event_id, ctrl.real_name, events.tsds, events.value,
> >
Heikki Linnakangas a écrit :
Arnaud Lesauvage wrote:
It is quite typical, yes. It is the base query of a view. In fact, most
views have a lot more joins (they join with all the upper-level tables).
But 150ms is OK, indeed.
If the query using the view does anything more than a "SELECT * FROM
Hi!
I have a problem with ACCESS EXCLUSIVE lock when I drop a reference in
transaction. I have 2 tables:
create table a(id SERIAL primary key);
create table b(id SERIAL primary key references a(id));
After that I have 2 processes: P1, P2
In P1:
begin;
ALTER TABLE b DROP CONSTRAINT b_id_fke
Arnaud Lesauvage wrote:
It is quite typical, yes. It is the base query of a view. In fact, most
views have a lot more joins (they join with all the upper-level tables).
But 150ms is OK, indeed.
If the query using the view does anything more than a "SELECT * FROM
view", you should do an explai
Heikki Linnakangas a écrit :
Arnaud Lesauvage wrote:
This query was taken from my "adminsitrative areas" model (continents,
countries, etc...). Whenever I query this model, I have to join many
tables.
I don't really know what the overhead of reading the heap-tuples is, but
would it be a good i
Arnaud Lesauvage wrote:
I did not know that joins were not using index values, and that
PostgreSQL had to fecth the heap tuples anyway.
Does this mean that this 2-column index is useless ? (I created it for
the join, I don't often filter on both columns otherwise)
Well, if no-one is using the
On 10/18/06, Tom Lane <[EMAIL PROTECTED]> wrote:
"Merlin Moncure" <[EMAIL PROTECTED]> writes:
> this is not really a jdbc issue, just a practical problem with
> prepared statements...
Specifically, that the OP is running a 7.4 backend, which was our
first venture into prepared parameterized stat
On 10/18/06, Heikki Linnakangas <[EMAIL PROTECTED]> wrote:
I would suggest using setting "prepareThreshold=0" in the JDBC driver
connection URL, or calling pstmt.setPrepareThreshold(0) in the
application. That tells the driver not to use server-side prepare, and
the query will be re-planned ever
Rohit_Behl wrote:
Hi
I made the following changes to the conf file:
enable_indexscan = true
enable_seqscan = false
We also have a large amount of data being inserted into our tables. I was just
wondering if this could have an impact on the inserts since I guess this change
is on the databas
Heikki Linnakangas a écrit :
Arnaud Lesauvage wrote:
I have two table with a 2-column index on both of them.
In the first table, the first colum of the index is the primary key, the
second one is an integer field.
In the second table, the two columns are the primary key.
When I join these two
Hi
I made the following changes to the conf file:
enable_indexscan = true
enable_seqscan = false
We also have a large amount of data being inserted into our tables. I was just
wondering if this could have an impact on the inserts since I guess this change
is on the database.
Please let me k
Arnaud Lesauvage wrote:
I have two table with a 2-column index on both of them.
In the first table, the first colum of the index is the primary key, the
second one is an integer field.
In the second table, the two columns are the primary key.
When I join these two tables, the 2-column index of
Hi list !
I have two table with a 2-column index on both of them.
In the first table, the first colum of the index is the primary key,
the second one is an integer field.
In the second table, the two columns are the primary key.
When I join these two tables, the 2-column index of the first tabl
Mario Weilguni wrote:
  contacto varchar(255),
  fuente varchar(512),
  prefijopais varchar(10)
Instead, use:
  contacto text,
  fuente text,
  prefijopais text
See the PostgreSQL manual for an explanation of varchar vs. text.
Enforcing length constraints with varchar(xyz
Am Dienstag, 17. Oktober 2006 17:50 schrieb Alexander Staubo:
> On Oct 17, 2006, at 17:29 , Mario Weilguni wrote:
> >
> > Enforcing length constraints with varchar(xyz) is good database
> > design, not a
> > bad one. Using text everywhere might be tempting because it works,
> > but it's
> > not a g
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
>> so, imo alexander is correct:
>> contacto varchar(255)
Why do we have limits on this, for example?
contacto varchar(255)
1) First of all, this is a web application. People use to enter really
strange thinks there, and a lot of rubbish. So, as s
50 matches
Mail list logo
