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 Postgres
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 varch
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, In
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 exclusi
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 simil
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
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
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, wh
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?
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);
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
>
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
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 t
John Beaver <[EMAIL PROTECTED]> writes:
> 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.
> Merge Join (cost=1399203593.41..6702491234.74 rows=352770803726
> width=22) (actual time=6370194.467..2299130
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
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
>
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 t
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 d
However there's a lot more scope for improving a query along these
lines, like adding indexes, or CLUSTERing on an index. It depends
what other queries you are wanting to run.
I don't know how much update/insert activity there will be on your
database. However, if you were to add an inde
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,
li
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 =
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, fs2.funct
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.
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);
Wh
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 m
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
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 *
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 matches
Mail list logo
