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)
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
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
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 explain me
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 [EMAIL PROTECTED] writes:
Can anyone explain me this ?
rvponp=# explain select * from tblprintjobs order by loginuser,
desceventdate, desceventtime offset 25 limit 25 ;
QUERY PLAN
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 that is
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
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
x-tad-biggerThese 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
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
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
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:
x-tad-biggerThese are my indexes
create index
rvponp=# explain analyze select * from tblPrintjobs order by loginuser, desceventdate, desceventtime ;
QUERY PLAN
Yves Vindevogel wrote:
rvponp=# explain analyze select * from tblPrintjobs order by
loginuser, desceventdate, desceventtime ;
QUERY PLAN
Sort
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
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,
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
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 = false?
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 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
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
--
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
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
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 of
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.
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
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
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
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 cache
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?
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
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 have
[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 the
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?
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 the
[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 to
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 tested
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. Bear in
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
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
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
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
-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 anyway?
[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 anyway
45 matches
Mail list logo
