On Jun 28, 2008, at 4:07 PM, Ulrich wrote:
Hi,
I have added a bit of dummy Data, 10 processors, 1 users,
each user got around 12 processors.
I have tested both queries. First of all, I was surprised that it is
that fast :) Here are the results:
EXPLAIN ANALYZE SELECT speed
On Jun 29, 2008, at 10:20 PM, Nimesh Satam wrote:
All,
While running a Select query we get the below error:
ERROR: out of memory
DETAIL: Failed on request of size 192.
Postgres Conf details:
shared_buffers = 256000
work_mem =15
max_stack_depth = 16384
max_fsm_pages = 40
version:
Anfang der weitergeleiteten E-Mail:
Von: Moritz Onken [EMAIL PROTECTED]
Datum: 30. Juni 2008 09:16:06 MESZ
An: Steinar H. Gunderson [EMAIL PROTECTED]
Betreff: Re: [PERFORM] Planner should use index on a LIKE 'foo%' query
Am 28.06.2008 um 21:19 schrieb Steinar H. Gunderson:
On Sat, Jun 28,
Am 28.06.2008 um 21:19 schrieb Steinar H. Gunderson:
On Sat, Jun 28, 2008 at 06:24:42PM +0200, Moritz Onken wrote:
SELECT distinct url from item where url like 'http://www.micro%'
limit
10;
Here, the planner knows the pattern beforehand, and can see that
it's a
simple prefix.
select *
I think it will be fast, because the IN set, which is the result of
SELECT processorid FROM users_processors WHERE userid=4040, is limited
to a maximum of ~500 processors which is not very big. Increasing
Postgres' RAM would be difficult for me, because I am only running a
very small server
On Jun 30, 2008, at 1:29 AM, Ulrich wrote:
I think it will be fast, because the IN set, which is the result
of SELECT processorid FROM users_processors WHERE userid=4040, is
limited to a maximum of ~500 processors which is not very big.
Increasing Postgres' RAM would be difficult for me,
On Mon, 30 Jun 2008, Moritz Onken wrote:
I created a new column in item where I store the shortened url which makes
= comparisons possible.
Good idea. Now create an index on that column.
select count(1) from result where url in (select shorturl from item where
shorturl = result.url);
What
Hi,
Le samedi 28 juin 2008, Moritz Onken a écrit :
select count(*)
from result
where exists
(select * from item where item.url LIKE result.url || '%' limit 1);
which basically returns the number of items which exist in table
result and match a URL in table item by its prefix.
It
Ok, here's the explain analyze result. Again, this is Postgres 8.3.3 and
I vacuumed-analyzed both tables directly after they were created.
# explain analyze select fls.function_verified, fls.score,
fls.go_category_group_ref, fs1.gene_ref, fs1.function_verified_exactly,
fs2.gene_ref,
Am 30.06.2008 um 12:19 schrieb Matthew Wakeling:
select count(1) from result where url in (select shorturl from item
where shorturl = result.url);
What on earth is wrong with writing it like this?
SELECT COUNT(*) FROM (SELECT DISTINCT result.url FROM result, item
WHERE
item.shorturl
On Mon, 30 Jun 2008, Moritz Onken wrote:
SELECT COUNT(*) FROM (SELECT DISTINCT result.url FROM result, item WHERE
item.shorturl = result.url) AS a
I tried the this approach but it's slower than WHERE IN in my case.
However there's a lot more scope for improving a query along these lines,
On 6/27/08, Chris Browne [EMAIL PROTECTED] wrote:
[EMAIL PROTECTED] (Josh Berkus) writes:
Jonah,
Hmm, I didn't think the Skype tools could really provide federated
database functionality without a good amount of custom work. Or, am I
mistaken?
Sure, what do you think
On Mon, Jun 30, 2008 at 9:16 AM, Marko Kreen [EMAIL PROTECTED] wrote:
But I want to clarify it's goal - it is not to run pre-determined
queries. It is to run pre-determined complex transactions.
Yes.
And to make those work in a federated database takes huge amount
of complexity that
Le Friday 27 June 2008, Scott Marlowe a écrit :
On Fri, Jun 27, 2008 at 8:23 AM, Nikhil G. Daddikar [EMAIL PROTECTED] wrote:
Hello,
I have been searching on the net on how to tune and monitor performance
of my postgresql server but not met with success. A lot of information is
vague and
Hello,
my understanding, and generally my experience, has been that VACUUM
and VACUUM ANALYZE (but not VACUUM FULL) are never supposed to block
neither SELECT:s nor UPDATE:s/INSERT:s/DELETE:s to a table.
This is seemingly confirmed by reading the explicit locking
documentation, in terms of the
Peter Schuller wrote:
Does anyone have input on why this could be happening? The PostgreSQL
version is 8.2.4[1]. Am I correct in that it *should* not be possible
for this to happen?
No. VACUUM takes an exclusive lock at the end of the operation to
truncate empty pages. (If it cannot get the
Hello,
No. VACUUM takes an exclusive lock at the end of the operation to
truncate empty pages. (If it cannot get the lock then it'll just skip
this step.) In 8.2.4 there was a bug that caused it to sleep
according to vacuum_delay during the scan to identify possibly empty
pages. This was
Actually, while on the topic:
date: 2007-09-10 13:58:50 -0400; author: alvherre; state: Exp; lines:
+6 -2;
Remove the vacuum_delay_point call in count_nondeletable_pages, because
we hold
an exclusive lock on the table at this point, which we want to release as
soon
as
Am 30.06.2008 um 16:59 schrieb Steinar H. Gunderson:
On Mon, Jun 30, 2008 at 09:16:06AM +0200, Moritz Onken wrote:
the result table has 20.000.000 records and the item table 5.000.000.
The query
select count(1) from result where url in (select shorturl from item
where shorturl = result.url);
On Mon, 30 Jun 2008, Moritz Onken wrote:
select count(1) from result where url in (select shorturl from item
where shorturl = result.url);
I really don't see what your query tries to accomplish. Why would you want
url IN (... where .. = url)? Wouldn't you want a different qualifier
somehow?
Peter Schuller wrote:
Actually, while on the topic:
date: 2007-09-10 13:58:50 -0400; author: alvherre; state: Exp;
lines: +6 -2;
Remove the vacuum_delay_point call in count_nondeletable_pages, because
we hold
an exclusive lock on the table at this point, which we want
The thing here is that you are effectively causing Postgres to run a
sub-select for each row of the result table, each time generating
either an empty list or a list with one or more identical URLs. This
is effectively forcing a nested loop. In a way, you have two
constraints where you
Hi,
I have problems with my database becoming huge in size (around 150 GB
right now, and 2/3 for only three tables, each having around 30 millions
tuples. Space is spent mainly on indices.).
I have a lot of multi-column varchar primary keys (natural keys), and
lot of foreign keys on these tables
Alvaro Herrera [EMAIL PROTECTED] writes:
Peter Schuller wrote:
Even with the fix the lock is held. Is the operation expected to be
fast (for some definition of fast) and in-memory, or is this
something that causes significant disk I/O and/or scales badly with
table size or similar?
It is
Alvaro Herrera wrote:
Peter Schuller wrote:
Actually, while on the topic:
date: 2007-09-10 13:58:50 -0400; author: alvherre; state: Exp;
lines: +6 -2;
Remove the vacuum_delay_point call in count_nondeletable_pages,
because we hold
an exclusive lock on the
Tom Lane wrote:
(2) If it's autovacuum we're talking about, it will get kicked off the
table if anyone else comes along and wants a conflicting lock.
Not on 8.2 though.
--
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt,
On Mon, 2008-06-30 at 18:57 +0200, Franck Routier wrote:
Hi,
I have problems with my database becoming huge in size (around 150 GB
right now, and 2/3 for only three tables, each having around 30 millions
tuples. Space is spent mainly on indices.).
I have a lot of multi-column varchar
chuckle You're right - for some reason I was looking at the (18
rows) at the bottom. Pilot error indeed - I'll have to figure out
what's going on with my data.
Thanks!
Tom Lane wrote:
John Beaver [EMAIL PROTECTED] writes:
Ok, here's the explain analyze result. Again, this is
28 matches
Mail list logo
