[Tom Lane - Tue at 05:20:07PM -0400]
>
> Certainly. Bear in mind though that DROP INDEX will acquire exclusive
> lock on the index's table, so until you roll back, no other transaction
> will be able to touch the table at all. So the whole thing may be a
> nonstarter in a production database any
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
>> Um, can't we just get that from pg_settings?
>>
>> Anyway, I'll be deriving settings from the .conf file, since most of the
>> time the Configurator will be run on a new installation.
>
> Aren't most of the settings all kept in the SHOW variables
Josh Berkus wrote:
Greg,
Not sure how far along you are, but I've been writing some really nifty
extensions to DBD::Pg that allow easy querying of all the current
run-time settings. Could be very useful to this project, seems to me. If
you're interested in possibly using it, let me know, I can
Greg,
> Not sure how far along you are, but I've been writing some really nifty
> extensions to DBD::Pg that allow easy querying of all the current
> run-time settings. Could be very useful to this project, seems to me. If
> you're interested in possibly using it, let me know, I can bump it up on
On Tue, Jun 21, 2005 at 11:08:43PM +0100, Alex Stapleton wrote:
> Bloody Debian stable. I might have to experiment with building from
> source or using alien on debian to convert the rpms. Fun. Oh well.
Or just pull in postgresql-8.0 from unstable; sid is close enough to sarge
for it to work qui
On 21 Jun 2005, at 18:13, Josh Berkus wrote:
Alex,
Downtime is something I'd rather avoid if possible. Do you think we
will need to run VACUUM FULL occasionally? I'd rather not lock tables
up unless I cant avoid it. We can probably squeeze an automated
vacuum tied to our data inserters every
I wonder if the -c parameter is truly submitting everything in parallel.
Having 2 telnet sessions up -- 1 doing -c 1 and another doing -c 100 --
I don't see much different in the display speed messages. Perhaps it's
an issue with the telnet console display limiting the command speed. I
thought
use CURRENT_TIME which is a constant instead of now() which is not
considered constant... (I think)
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Tobias Brox <[EMAIL PROTECTED]> writes:
> [John A Meinel - Tue at 10:14:24AM -0500]
>> I believe if you drop the indexes inside a transaction, they will still
>> be there for other queries, and if you rollback instead of commit, you
>> won't lose anything.
> Has anyone tested this?
Certainly. Be
On Tue, Jun 21, 2005 at 09:46:39PM +0200, Tobias Brox wrote:
> [John A Meinel - Tue at 10:14:24AM -0500]
> > I believe if you drop the indexes inside a transaction, they will still
> > be there for other queries, and if you rollback instead of commit, you
> > won't lose anything.
>
> Has anyone te
[Oliver Crosby - Tue at 03:46:03PM -0400]
> I'm hoping someone can offer some advice here.
> I have a large perl script that employs prepared statements to do all its
> queries. I'm looking at using stored procedures to improve performance times
> for the script. Would making a stored procedure
Some tips:
- EXPLAIN ANALYZE provides a more useful analysis of a slow query,
because it gives both the estimate and actual times/rows for each step
in the plan.
- The documentation is right: rows with little variation are pretty
useless to index. Indexing is about "selectivity", reducing th
I'm hoping someone can offer some advice here.
I have a large perl script that employs prepared statements to do all its queries. I'm looking at using stored procedures to improve performance times for the script. Would making a stored procedure to replace each prepared statement be worthwhile? I
[John A Meinel - Tue at 10:14:24AM -0500]
> I believe if you drop the indexes inside a transaction, they will still
> be there for other queries, and if you rollback instead of commit, you
> won't lose anything.
Has anyone tested this?
(sorry, I only have the production database to play with at t
Ok, tnx !!
On 21 Jun 2005, at 18:54, John A Meinel wrote:
Yves Vindevogel wrote:
I only add records, and most of the values are "random"
Except the columns for dates,
I doubt that you would need to recreate indexes. That really only needs
to be done in pathological cases, most of which hav
Hello!I use FreeBSD 4.11 with PostGreSQL 7.3.8.I got a huge database with roughly 19 million records. There is just onetable, with a time field, a few ints and a few strings.table testfields time (timestamp), source (string), destination (string), p1 (int),
p2 (int)I have run VACUUM ANALYZE ;I have
Folks,
OK, I've checked in my first code module and the configurator project is
officially launched. Come join us at
www.pgfoundry.org/projects/configurator
Further communications will be on the Configurator mailing list only.
from the spec:
What is the Configurator, and Why do We Need It?
-
On 6/21/05, PFC <[EMAIL PROTECTED]> wrote:
...
> In your case I don't think that is the solution, because you do big
> updates. With triggers this would mean issuing one update of your
> materialized view per row in your big update. This could be slow.
>
> In this case you might want to update the
Alex,
> Downtime is something I'd rather avoid if possible. Do you think we
> will need to run VACUUM FULL occasionally? I'd rather not lock tables
> up unless I cant avoid it. We can probably squeeze an automated
> vacuum tied to our data inserters every now and then though.
As long as your upda
From what you say I understand that you have a huge table like this :
( name, value, id )
And you want to make statistics on (value) according to (name,id).
***
First of all a "materialized view" doen't exist in postgres, it's just a
word to n
Yves Vindevogel wrote:
I only add records, and most of the values are "random"
Except the columns for dates,
I doubt that you would need to recreate indexes. That really only needs
to be done in pathological cases, most of which have been fixed in the
latest postgres.
If you are only ins
I only add records, and most of the values are "random"
Except the columns for dates,
On 21 Jun 2005, at 17:49, John A Meinel wrote:
Yves Vindevogel wrote:
And, after let's say a week, would that index still be optimal or
would it be a good idea to drop it in the weekend and recreate it.
My Dual Core Opteron server came in last week. I tried to do some
benchmarks with pgbench to get some numbers on the difference between
1x1 -> 2x1 -> 2x2 but no matter what I did, I kept getting the same TPS
on all systems. Any hints on what the pgbench parameters I should be using?
In terms o
I had a similar experience.
regardless of scaling, etc, I got same results. almost like flags
are not active.
did
pgbench -I template1
and
pgbench -c 10 -t 50 -v -d 1
and played around from there
This is on IBM pSeries, AIX5.3, PG8.0.2
-Original Message-
From: [EMAIL PROTECTED]
--
Don
Vaillancourt
Director of Software Development
WEB
IMPACT INC.
phone: 416-815-2000 ext. 245
fax: 416-815-2001
email: [EMAIL PROTECTED]
web: http://www.web-impact.com
address: http://www.mapquest.ca
First of all, thanks to everyone for helping me !
Looks like materialized views will be my answer.
Let me explain my situation a little better.
The repository table looks like this -
create table repository (statName varchar(45), statValue varchar(45),
metaData varchar(45));
MetaData is a for
Yves Vindevogel wrote:
And, after let's say a week, would that index still be optimal or
would it be a good idea to drop it in the weekend and recreate it.
It depends a little bit on the postgres version you are using. If you
are only ever adding to the table, and you are not updating it or
d
And, after let's say a week, would that index still be optimal or would it be a good idea to drop it in the weekend and recreate it.
On 21 Jun 2005, at 17:22, John A Meinel wrote:
Yves Vindevogel wrote:
Hi,
I have another question regarding indexes.
I have a table with a lot of indexes on it.
Yves Vindevogel <[EMAIL PROTECTED]> writes:
> create index ixPrintjobsLoginDescEventdateDesceventtime on
> tblPrintjobs (loginuser, desceventdate, desceventtime) ;
Hmm, that certainly looks like it should match the query. What happens
to the EXPLAIN output if you do "set enable_sort = fals
Yves Vindevogel wrote:
Hi,
I have another question regarding indexes.
I have a table with a lot of indexes on it. Those are needed to
perform my searches.
Once a day, a bunch of records is inserted in my table.
Say, my table has 1.000.000 records and I add 10.000 records (1% new)
What would
Jone C wrote:
On second thought... Does a VACUUM FULL help? If so, you might want to
increase your FSM settings.
Thank you for the reply, sorry for delay I was on holiday.
I tried that it had no effect. I benchmarked 2x before, peformed
VACUUM FULL on the table in question post inserts, the
Hi,
I have another question regarding indexes.
I have a table with a lot of indexes on it. Those are needed to perform my searches.
Once a day, a bunch of records is inserted in my table.
Say, my table has 1.000.000 records and I add 10.000 records (1% new)
What would be faster.
1) Dropping m
Yves Vindevogel wrote:
rvponp=# explain analyze select * from tblPrintjobs order by
loginuser, desceventdate, desceventtime ;
QUERY PLAN
Sort (cost=345699.06..347256.
rvponp=# explain analyze select * from tblPrintjobs order by loginuser, desceventdate, desceventtime ;
QUERY PLAN
-
Nevermind guys
There's an error in a function that is creating these indexes.
The function never completed succesfully so the index is not there
Very sorry about this !!
On 21 Jun 2005, at 16:57, Yves Vindevogel wrote:
These are my indexes
create index ixprintjobsapplicationtype on tblPri
> On second thought... Does a VACUUM FULL help? If so, you might want to
> increase your FSM settings.
Thank you for the reply, sorry for delay I was on holiday.
I tried that it had no effect. I benchmarked 2x before, peformed
VACUUM FULL on the table in question post inserts, then benchmarked 2x
Amit V Shah wrote:
After I sent out this email, I found this article from google
http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html
Looks like we can control as to when the views refresh... I am still kind of
confused, and would appreciate help !!
The create/drop table doe
These are my indexes
create index ixprintjobsapplicationtype on tblPrintjobs (applicationtype);
create index ixprintjobsdesceventdate on tblPrintjobs (desceventdate);
create index ixprintjobsdesceventtime on tblPrintjobs (desceventtime);
create index ixprintjobsdescpages on tblPrintjobs (descpages
However, I donot know if the query that creates the view is executed
everytime I select something from the view. Because if that is the case,
then I think my queries will again be slow. But if that is the way views
work, then what would be the point in creating them ..
Views are more for whe
Amit V Shah wrote:
Hi all,
I have like a repository table with is very very huge with atleast a few
hundreds of millions, may be over that. The information is stored in form of
rows in these tables. I need to make that information wide based on some
grouping and display them as columns on the sc
After I sent out this email, I found this article from google
http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html
Looks like we can control as to when the views refresh... I am still kind of
confused, and would appreciate help !!
The create/drop table does sound a solution tha
On 6/21/05, Amit V Shah <[EMAIL PROTECTED]> wrote:
> Hi all,
...
> I am thinking of having a solution where I create views for each screen,
> which are just read only.
>
> However, I donot know if the query that creates the view is executed
> everytime I select something from the view. Because if
Yves Vindevogel <[EMAIL PROTECTED]> writes:
> Can anyone explain me this ?
> rvponp=# explain select * from tblprintjobs order by loginuser,
> desceventdate, desceventtime offset 25 limit 25 ;
> QUERY PLAN
> ---
Yves Vindevogel wrote:
Hi,
I have a very simple query on a big table. When I issue a "limit"
and/or "offset" clause, the query is not using the index.
Can anyone explain me this ?
You didn't give enough information. What does you index look like that
you are expecting it to use?
Generally
Yves Vindevogel wrote:
> Hi,
>
> rvponp=# explain select * from tblprintjobs order by loginuser,
> desceventdate, desceventtime offset 25 limit 25 ;
>
> I have a very simple query on a big table. When I issue a "limit"
> and/or "offset" clause, the query is not using the index.
> Can anyone exp
Hi,
I have a very simple query on a big table. When I issue a "limit" and/or "offset" clause, the query is not using the index.
Can anyone explain me this ?
rvponp=# explain select * from tblprintjobs order by loginuser, desceventdate, desceventtime offset 25 limit 25 ;
QUERY PLAN
Hi all,
I have like a repository table with is very very huge with atleast a few
hundreds of millions, may be over that. The information is stored in form of
rows in these tables. I need to make that information wide based on some
grouping and display them as columns on the screen.
I am thinking
On 20 Jun 2005, at 18:46, Josh Berkus wrote:
Alex,
Hi, i'm trying to optimise our autovacuum configuration so that it
vacuums / analyzes some of our larger tables better. It has been set
to the default settings for quite some time. We never delete
anything (well not often, and not much) f
48 matches
Mail list logo
