Function Scan on getTimeSeries (cost=0.00..12.50 rows=1000 width=24)
(actual time=11065.981..11067.008 rows=262 loops=1)
Total runtime: 11067.991 ms
(2 rows)
It is a PL function. Do I need to break it down?
A. Kretschmer wrote:
am Sat, dem 23.12.2006, um 11:26:08 -0800 mailte Benjamin Arai
Hi,
The largest table in my database (30GB) has mysteriously went from
taking milli-seconds to perform a query to minutes. This disks are fine
and I have a 4GB shared_memory. Could this slow down have to do with
the fsm_max_pages or something else like that? I made it larger but the
I thought that you only need to use the -z flag if the distribution of
the data is changing.
Jeff Frost wrote:
On Sat, 23 Dec 2006, Benjamin Arai wrote:
The largest table in my database (30GB) has mysteriously went from
taking milli-seconds to perform a query to minutes. This disks
I have been running pieces of my PL function by hand and I have found
that the following queries work by themselves taking less than a second
to execute.
getDateRange('12/1/2005','12/1/2006') - simply generates a date
list. Doesn't even access a table
SELECT * FROM mutualfd_weekday_qbid
, *Benjamin Arai* [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:
I have been running pieces of my PL function by hand and I
have found
that the following queries work by themselves taking less than
a second
to execute.
getDateRange('12/1/2005','12
Hi,
I did a vacuum with -z and it fixed the issue. I was not aware that
vacuumdb didn't ANALYZE by default.Thanks everybody for all of the help!
Benjamin
Tom Lane wrote:
Benjamin Arai [EMAIL PROTECTED] writes:
- Index Scan using mutualfd_weekday_qbid_pkey_idx
; - fast
It just has to do with the new data for some reason.
Benjamin
Benjamin Arai wrote:
Function Scan on getTimeSeries (cost=0.00..12.50 rows=1000
width=24) (actual time=11065.981..11067.008 rows=262 loops=1)
Total runtime: 11067.991 ms
(2 rows)
It is a PL function. Do I need to break
Hi,
I am trying to backup an installation (8.0.3) and restore it to a new
(8.2.1) installation. I am am running the following command the backup
the data.
pg_dumpall outfile
Then on the new machine I am simply running:
ENCODING=UTF8 psql -f outfile postgres
I get the following error for
Hi,
I have a really big Tsearch2 table (100s GB) that takes a while to perform
queries and takes days to index. Is there any way to fix these issues
using UNIONs or partitioning? I was thinking that I could partition the
data by date but since I am always performing queries on the Tsearch2
Hi,
I have a really big Tsearch2 table (100s GB) that takes a while to perform
queries and takes days to index. Is there any way to fix these issues
using UNIONs or partitioning? I was thinking that I could partition the
data by date but since I am always performing queries on the Tsearch2
Benjamin Arai wrote:
Hi,
I have a really big Tsearch2 table (100s GB) that takes a while to
perform
queries and takes days to index. Is there any way to fix these issues
using UNIONs or partitioning? I was thinking that I could partition the
data by date but since I am always performing
and big archive table with static data and
GIN index. I have some slides from PGDay
http://www.sai.msu.su/~megera/postgres/talks/fts-pgday-2007.pdf
Also, did you consider using dblink/dbilink to scale your search ?
Oleg
On Thu, 12 Jul 2007, Benjamin Arai wrote:
Hi,
I have a really big Tsearch2
Hi,
If I have a query such as:
SELECT * FROM (SELECT * FROM A) UNION ALL (SELECT * FROM B) WHERE
blah='food';
Assuming the table A and B both have the same attributes and the data
between the table is not partitioned in any special way, does Postgresql
execute WHERE blah=food on both table
I get the following error when tying to index a large text field:
dev=# CREATE INDEX idx_fulltext_articel_block ON fulltext_article
(article_block);
ERROR: index row requires 8724 bytes, maximum size is 8191
I am actually creating a GIN index on another field but I need to
index the
(1 row)
Which would mean I would have to create index on strMessage. Right?
Benjamin
On Aug 5, 2007, at 7:17 PM, Tom Lane wrote:
Benjamin Arai [EMAIL PROTECTED] writes:
I am actually creating a GIN index on another field but I need to
index the original text field to perform exact phrase
Hi,
I have an application which loads millions of NEW documents each month
into a PostgreSQL tsearch2 table. I have the initial version completed
and searching performance is great but my problem is that each time a
new
month rolls around I have to drop all the indexes do a COPY and re-index
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
This kind of disappointing, I was hoping there was more that could be
done.
There has to be another way to do incremental indexing without
loosing that much performance.
Benjamin
On Aug 24, 2007, at 6:29 PM, Joshua D. Drake wrote:
PGP SIGNED MESSAGE-
Hash: SHA1
Benjamin Arai wrote:
This kind of disappointing, I was hoping there was more that could
be done.
There has to be another way to do incremental indexing without
loosing
that much performance.
What makes you think you are loosing performance by using
MESSAGE-
Hash: SHA1
Benjamin Arai wrote:
As stated in the previous email if I use partitioning then queries
will
be executed sequentially - i.e., instead of log(n) it would be (#
partitions) * log(n). Right?
The planner will consider every relevant partition during the
execution.
Which may
Hi,
So, I built my tables which contains a TSearch2 field by
1. Create table without indexes
2. COPY data into table
3. ALTER TABLE tblMessages ADD COLUMN idxFTI tsvector;
4. UPDATE tblMessages SET idxFTI=to_tsvector('default', strMessage);
5. Index all the fields including the TSearch2 field
:
On Sun, 26 Aug 2007, Benjamin Arai wrote:
So, I built my tables which contains a TSearch2 field by
1. Create table without indexes
2. COPY data into table
3. ALTER TABLE tblMessages ADD COLUMN idxFTI tsvector;
4. UPDATE tblMessages SET idxFTI=to_tsvector('default',
strMessage);
Or you could
Is there a way to pass a query to PostgreSQL to check if the
TSeasrch2 search text is valid? For example,
SELECT to_tsquery('default', '!');
returns an error. I want to know if there is a way get true/false
for the '!' portion of the query?
Benjamin
---(end of
() and querytree()
More details in http://www.sai.msu.su/~megera/postgres/fts/doc/fts-
query.html
Oleg
On Sat, 8 Sep 2007, Benjamin Arai wrote:
Is there a way to pass a query to PostgreSQL to check if the
TSeasrch2 search text is valid? For example,
SELECT to_tsquery('default', '!');
returns
, Tom Lane wrote:
Benjamin Arai [EMAIL PROTECTED] writes:
Is there a way to pass a query to PostgreSQL to check if the
TSeasrch2 search text is valid? For example,
SELECT to_tsquery('default', '!');
returns an error. I want to know if there is a way get true/false
for the '!' portion of the query
Hi,
I have very slow performance for a TSearch2 table. I have pasted the
EXPLAIN ANALYZE queries below. 12 seconds is slow for almost any
purpose. Is there any way to speed this up?
# explain analyze select * FROM fulltext_article, to_tsquery
('simple','dog') AS q WHERE idxfti @@ q
On Oct 5, 2007, at 8:32 AM, Oleg Bartunov wrote:
On Fri, 5 Oct 2007, Tom Lane wrote:
Benjamin Arai [EMAIL PROTECTED] writes:
# explain analyze select * FROM fulltext_article, to_tsquery
('simple','dog') AS q WHERE idxfti @@ q ORDER BY rank(idxfti, q)
DESC;
QUERY PLAN
It appears that the ORDER BY rank operation is the slowing factor.
If I remove it then the query is pretty fast. Is there another way
to perform ORDER BY such that it does not do a sort?
Benjamin
On Oct 5, 2007, at 3:57 PM, Benjamin Arai wrote:
On Oct 5, 2007, at 8:32 AM, Oleg Bartunov
Oh, I see. I didn't look carefully at the EXPLAIN ANALYZE I posted.
So, is there a solution to the rank problem?
Benjamin
On Oct 11, 2007, at 8:53 AM, Tom Lane wrote:
Benjamin Arai [EMAIL PROTECTED] writes:
It appears that the ORDER BY rank operation is the slowing factor.
If I remove
In what order should I :
- COPY data
- Create indexes
- Create Trigger
- Vaccum
?
Currently I am:
1. Create table
2 . Create trigger for updates
3. Create indexes including gin
4. Vaccum
Benjamin
On Aug 27, 2007, at 7:59 AM, Tom Lane wrote:
Benjamin Arai [EMAIL PROTECTED] writes:
Why
Hi,
Is there a way to give priorities to queries or users? Something
similar to NICE in Linux. My goal is to give the updating (backend)
application a very low priority and give the web application a high
priority to avoid disturbing the user experience.
Thanks in advance!
Benjamin
Hi,
I have a database (200GB+), I need to upload about 10GB of data each
week. There are no deletions. My problem is that inserting takes a
very long time due to the indexes. I can speedup inserting the data
insertion if I drop the indexes but then I am left with the problem of
Just to clarify, there is no way to throttle specific queries or users
in PostgreSQL?
Benjamin
Joshua D. Drake wrote:
Benjamin Arai wrote:
Hi,
Is there a way to give priorities to queries or users? Something
similar to NICE in Linux. My goal is to give the updating (backend
Hi Edwin,
Which connection parameters effect system resources?
Benjamin
Edwin Eyan Moragas wrote:
On 2/11/07, Benjamin Arai [EMAIL PROTECTED] wrote:
Hi,
Is there a way to give priorities to queries or users? Something
similar to NICE in Linux. My goal is to give the updating (backend
-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Benjamin Arai
Sent: Saturday, February 10, 2007 6:56 PM
To: pgsql-general@postgresql.org; pgsql-admin@postgresql.org
Subject: [GENERAL] Priorities for users or queries?
Hi,
Is there a way to give priorities to queries or users
Hi Jan,
That makes sense. Does that mean that a low-priority road-block can
cause a deadlock or just an very long one lock?
Benjamin
Jan Wieck wrote:
On 2/11/2007 1:02 PM, Benjamin Arai wrote:
Hi Magnus,
Think this can be avoided as long the the queries executed on the
lower priority
Fair enough, thanks for the clarification.
Benjamin
Jan Wieck wrote:
On 2/16/2007 4:56 PM, Benjamin Arai wrote:
Hi Jan,
That makes sense. Does that mean that a low-priority road-block
can cause a deadlock or just an very long one lock?
It doesn't cause any deadlock by itself. Although
with
commercial solutions (i.e. Oracle, DB2).
Would this be difficult to implement? Maybe a summer of code person
could do it.
Benjamin Arai
Jan Wieck wrote:
On 2/16/2007 5:05 PM, Benjamin Arai wrote:
Fair enough, thanks for the clarification.
What you can do to throttle things in a reasonable
My problem with [1] is that even for 10 users the percentage of time
spent in locks is very high. Can priorities scale?
Benjamin
Ron Mayer wrote:
Bruce Momjian wrote:
Hard to argue with that.
Is it a strong enough argument to add a TODO?
I'm thinking some sort of TODO might be
My problem with [1] is that even for 10 users the percentage of time
spent in locks is very high. Can priorities scale?
Benjamin
Ron Mayer wrote:
Bruce Momjian wrote:
Hard to argue with that.
Is it a strong enough argument to add a TODO?
I'm thinking some sort of TODO might be
Hi,
If you are looking for a SoC idea, I have listed a couple below. I
am not sure how good of an idea they are but I have ran into the
following limitations and probably other people have as well in the
past.
1. Can user based priorities be implemented as a summer project? To
some
I wonder if converting all of your bools to a bit string and storing
that string as a number would make things faster?
Benjamin
On Mar 20, 2007, at 11:54 AM, Jonathan Vanasco wrote:
i'm going crazy trying to optimize this select.
The table has ~25 columns, the select is based on 10.
Hi,
I have been struggling with getting fulltext searching for very large
databases. I can fulltext index 10s if gigs without any problem but
when I start geting to hundreds of gigs it becomes slow. My current
system is a quad core with 8GB of memory. I have the resource to
throw more
On Mar 21, 2007, at 8:42 AM, Oleg Bartunov wrote:
Benjamin,
as one of the author of tsearch2 I'd like to know more about your
setup.
tsearch2 in 8.2 has GIN index support, which scales much better
than old
GiST index.
Oleg
On Wed, 21 Mar 2007, Benjamin Arai wrote:
Hi,
I have been struggling
might be able to do something better.
Benjamin
On Mar 21, 2007, at 8:49 AM, Joshua D. Drake wrote:
Benjamin Arai wrote:
Hi,
I have been struggling with getting fulltext searching for very large
databases. I can fulltext index 10s if gigs without any problem but
when I start geting to hundreds
.
tsearch2 in 8.2 has GIN index support, which scales much better
than old
GiST index.
Oleg
On Wed, 21 Mar 2007, Benjamin Arai wrote:
Hi,
I have been struggling with getting fulltext searching for very
large databases. I can fulltext index 10s if gigs without any
problem but when I start
24.
Benjamin
On Mar 21, 2007, at 9:09 AM, Joshua D. Drake wrote:
Benjamin Arai wrote:
True, but what happens when my database reaches 100 terabytes? Is 5
seconds ok? How about 10? My problem is that I do not believe the
performance loss I am experiencing as the data becomes large is
(log
Can't you implement something similar to google by aggregating
results for TSearch2 over many machines?
Benjamin
On Mar 21, 2007, at 8:59 AM, Teodor Sigaev wrote:
I'm afraid that fulltext search on multiterabytes set of documents
can not be implemented on any RDBMS, at least on single box.
What is inheritance+CE?
Benjamin
On Mar 21, 2007, at 9:10 AM, Oleg Bartunov wrote:
inheritance+CE
Hi,
If you are looking for a SoC idea, I have listed a couple below. I
am not sure how good of an idea they are but I have ran into the
following limitations and probably other people have as well in the
past.
1. Can user based priorities be implemented as a summer project? To
some
Hi,
I have been struggling with getting fulltext searching for very large
databases. I can fulltext index 10s if gigs without any problem but
when I start geting to hundreds of gigs it becomes slow. My current
system is a quad core with 8GB of memory. I have the resource to
throw more
Are there any examples of dblink being used in commercial
environments. I am curious to understand how it deals with node
failures and etc.
Benjamin
On Mar 21, 2007, at 9:35 AM, Oleg Bartunov wrote:
On Wed, 21 Mar 2007, Benjamin Arai wrote:
Can't you implement something similar
Hi,
If I have a PostgreSQL table with records and logical indexes already
created, if I use COPY to load additional data, does the COPY update
the indexes during, after, or not at all?
Benjamin
---(end of broadcast)---
TIP 1: if
:
am Thu, dem 29.03.2007, um 0:13:09 -0700 mailte Benjamin Arai
folgendes:
Hi,
If I have a PostgreSQL table with records and logical indexes already
created, if I use COPY to load additional data, does the COPY update
the indexes during, after, or not at all?
after, i think.
test=# create
:
am Thu, dem 29.03.2007, um 0:13:09 -0700 mailte Benjamin Arai
folgendes:
Hi,
If I have a PostgreSQL table with records and logical indexes already
created, if I use COPY to load additional data, does the COPY update
the indexes during, after, or not at all?
after, i think.
test=# create
I would prefer not to drop the index because the database is several
hundred gigs. I would prefer to incrementally add to the index.
Benjamin
Bruce Momjian wrote:
Benjamin Arai wrote:
So, is there a way to defer the index updating until a later period
of time. More specifically, I
... This makes it a little more of a pain in the ass.
Benjamin
Tom Lane wrote:
Benjamin Arai [EMAIL PROTECTED] writes:
I would prefer not to drop the index because the database is several
hundred gigs. I would prefer to incrementally add to the index.
This may well be false economy
-29 at 21:30 -0700, Benjamin Arai wrote:
Rebuilding an index can't be the PostgreSQL solution for all
cases. I am dealing with databases in the hundreds of gigs
range and I am adding about 10gigs of data a week. At
some point its going to take longer than a week to rebuild
all of the indexes
Hi,
By separating tables and indexes onto different drives through the use
of tablespaces does this increase COPY or CREATE INDEX performance? If
you have a ballpark figure of how much gain if any, that would be great too.
Benjamin
---(end of
Hi,
What is the resulting dump (pg_dump) from a database with multiple
tables spaces? Does the dump just strip off the TABLESPACE command, so
it will lump all the tablespaces into the default tablespace?
Benjamin
---(end of broadcast)---
TIP
Can anybody explain to me why this query is executing so slow?
=# explain select s_content,textdir from text_search where
path_id='1' AND tb_id='P2_TB1';
QUERY PLAN
)
Benjamin
On Apr 23, 2007, at 3:38 PM, Benjamin Arai wrote:
Can anybody explain to me why this query is executing so slow?
=# explain select s_content,textdir from text_search where
path_id='1' AND tb_id='P2_TB1';
QUERY PLAN
:
Benjamin Arai [EMAIL PROTECTED] writes:
To follow up on my own email, by disabling BitmapScan in my
postgresql.conf the performance seems to be better. Is something
wrong with the query analyzer?
I just rewrote choose_bitmap_and() to fix some issues that might be
related to this; please try
.
Benjamin
On Apr 24, 2007, at 1:12 AM, Alban Hertroys wrote:
Benjamin Arai wrote:
Hi,
I upgraded to 8.2.4 but there was no significant change in
performance.
I did notice that hte query appears to be executed incorrectly.
I have pasted the EXPLAIN ANALYZE below to illustrate:
=# explain
Hello,
I was reading the docuementation for 8.3
(http://www.postgresql.org/docs/8.3/static/wal-async-commit.html) and it
states An immediate-mode shutdown is equivalent to a server crash, and
will therefore cause loss of any unflushed asynchronous commits.. Does
this mean that doing a
Hello,
We are running a system which requires continual uptime while loading
data. Currently one particular table receives a large number of inserts
per commit (about 1 inserts). This process works well allowing both
end users to access the data as well as loading reasonably quickly.
Has anybody had a good experience going from Npgsql to
Corelab:PostgreSQLDirect? I am considering migrating because it offers
support for COPY but I am wondering about other functionality and
performance as well. Any feedback would be greatly appreciated.
Benjamin
Hello,
Does anybody know of the performance impact of using uuid over int4?
Specifically, I am assuming that it will be about 4 times slower since
it is 128 bits. Is this correct?
Benjamin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your
What is the current maximum number of tables per database? Also, does
having more tables slow down performance in any way?
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
My issue actually stems from the fact that I cannot do large weekly
updates on fast enough to meet a weekend window for the following work
week. I am currently using a machine with a raid 1, 4GB RAM, and dual
opteron. I could go 0+1 but peroformance increase is only about 20%
from the
using Postgresql 8.0.3.
Example command UPDATE data where name=x and date=y;.
Martijn van Oosterhout wrote:
On Sat, Dec 10, 2005 at 03:22:47PM -0800, Benjamin Arai wrote:
My issue actually stems from the fact that I cannot do large weekly
updates on fast enough to meet a weekend window
What kind of performance boost do you get from using raid 10? I am trying
to do a little cost analysis.
Benjamin Arai
[EMAIL PROTECTED]
[EMAIL PROTECTED]
http://www.benjaminarai.com
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
[EMAIL
Each week I have to
update a very large database. Currently I run a commit about every
1000 queries. This vastly increased performance but I am wondering if the
performance can be increased further. I could send all of the queries to a
file but COPY doesn't support plain queries such as
A machine with 4GB
of memory has the ability to allocate 2GB to postgres under kenel 2.6. Is
the 2GB maximum the sum total of (work_mem, maitenance_work_mem, and
shared_memory) or just
shared_memory?
I want to be able to do large updates on an existing backed up database
with fsync=off but at the end of the updates how do I ensure that the
data gets synced?
Somebody said running sync ; sync; sync from the console. This seems
reasonable but why not just sync or is there another command I
Are there any compatability issues from dumping a database from a x86
installation and restoring it on a 64bit install or vice versa? I am
assuming I can go from x86 to 64 but I am not sure about the other
way. Any information would be greatly appreciated.
--
Benjamin Arai
[EMAIL PROTECTED
Is there a way to transfer data between the two architectures?
Tom Lane wrote:
Benjamin Arai [EMAIL PROTECTED] writes:
Are there any compatability issues from dumping a database from a x86
installation and restoring it on a 64bit install or vice versa?
It won't work
I thought you said that it would not work in either direction?
Joshua D. Drake wrote:
Benjamin
Arai wrote:
Is there a way to transfer data between the
two architectures?
I would think you could do it over the network:
pg_dump -U postgres foo|psql -U postgres -h
Oh, great. Thanks.
Tom Lane wrote:
Benjamin Arai [EMAIL PROTECTED] writes:
Is there a way to transfer data between the two architectures?
Sure: pg_dump. But you can't just move the physical database files,
any more than you could do across completely different
Hi,
Is it possible to create a view from two different database tables?
--
Benjamin Arai
[EMAIL PROTECTED]
http://www.benjaminarai.com
took too long for slony to catch up and there is no real need
to do replication during the update. Does that seem like the best
option?
Benjamin Arai
[EMAIL PROTECTED]
http://www.benjaminarai.com
smime.p7m
Description: S/MIME encrypted message
Is the PL support in
EnterpriseDB worth the money? Are there any specific benefits that I
should specifically be aware of?
Benjamin Arai
[EMAIL PROTECTED]
http://www.benjaminarai.com
and DB2.
Benjamin Arai
[EMAIL PROTECTED]
http://www.benjaminarai.com
Hi,
Myself and a friend are PhD students at the University of California,
Riverside. We would be interested in such a project if it were available.
We are both experienced developers previously interning at EA Games and
current system administrators for the CS department.
Benjamin Arai
[EMAIL
index
going to be what I want, or do I need a index that contains both name and
date?
Benjamin
Arai
[EMAIL PROTECTED]
http://www.benjaminarai.com
BEGIN:VCARD
VERSION:2.1
N:Arai;Benjamin
FN:[EMAIL PROTECTED]
ORG:University of California, Riverside
TITLE:PhD Student
TEL;WORK;VOICE:(951) 827-2838
Hello,
I have a data sets where each of the objects is represented in a metric
space with 32 dimensions (i.e., each object is represented by 32 numbers).
Is there a way to represent this object in Postgresql so that I can perform
KNN?
Thanks,
Benjamin
86 matches
Mail list logo
