With Postgresql 8.1.9 -- I have a simple group by query:
SELECT target_page_id, min(created_at)
FROM page_page_link
GROUP BY 1;
The page_page_link table has ~130 million rows.
After analyzing the table, the planner picks a hash aggregate plan, which
results in an out of memory error.
crystal=>
Thanks Tom. Here's more info:
What have you got work_mem set to?
40960
What's the actual number of groups
(target_page_id values)?
Approximately 40 million (I'll have a more precise number when the query
finishes running ).
Maybe this helps?
crystal=> select null_frac, n_distinct, correl
/14/07, Mason Hale <[EMAIL PROTECTED]> wrote:
Thanks Tom. Here's more info:
What have you got work_mem set to?
40960
What's the actual number of groups
> (target_page_id values)?
Approximately 40 million (I'll have a more precise number when the query
finishes runn
On a 8.1.9 version database that has been recently vacuumed and
analyzed, I'm seeing some dramatic performance degradation if a limit
clause is included in the query. This seems counter-intuitive to me.
Here's the query and explain plan WITH the LIMIT clause:
SELECT *
FROM topic_feed
WHERE to
> Let's call those plan 1 and plan 2.
>
> In plan 1, the planner thinks that it will find 25 tuples matching that
> topic_id quickly during the backwards index scan on
> topic_feed_score_index. Instead, it looks like it has to go through a
> lot of tuples before it finds the necessary 25.
>
> In pl
If you haven't seen it already, there is a rails plugin that adds
support for foreign-key dependencies (among other things) to the
migration domain specific language (DSL):
http://www.redhillonrails.org/#foreign_key_migrations
Another useful plug-in is "Transactional Migrations" which
automatical
We're noticing a number of deadlock errors in our postgres logs.
Along with the errors, there is a single statement logged to give
context to the deadlock.
Is is possible to log all the statements involved in the deadlock?
We're having a hard time detecting these deadlocks when they happen,
and it
I am setting up a warm standby configuration as described here:
http://www.postgresql.org/docs/8.2/static/warm-standby.html
Using PostgreSql 8.2.5
My production server is archiving 16MB wal segment files at a rate of 1
every 5 to 10 seconds
My standby server is processing the wal segment files a
> your i/o must be really random to be seeing numbers that lousy (10
> seconds to replay a file is 1.6 megabytes/sec), or there is some other
> unexplained problem with your server. is your raid controller
> properly caching wites? have you benchmarked the volume with bonnie++
> or similar tool (
I'm implementing table partitioning on 8.2.5 -- I've got the tables set up
to partition based on the % 10 value of a key.
My problem is that I can't get the planner to take advantage of the
partitioning without also adding a key % 10 to the where clause.
Is there any way around that?
My child tabl
>
> Well, given that the bin is computed as a function of some_id, the
> most natural way would be to not have to mention that bin in SELECT
> statements at all. However, it does appear that either a.) including
> the bin as a table attribute and in the where clause (either directly
> or the compu
I recently discovered the hashtext() function, and I'm interested in using
it to reduce the size of one of the biggest indexes in my database.
I have a table of URLs with a unique index on the URL. Some of these URLs
are very long (we truncate them at 1024 characters), and we have many
millions of
I recently discovered the hashtext() function, and I'm interested in using
> it to reduce the size of one of the biggest indexes in my database.
>
...
The problem with either MD5 or hashtext() is that neither can guarantee
> unique output even if the inputs are all unique.
>
...
>
> The problem I
Hello, I've got a 9.3 database hosted at Heroku.
I'm full text search to search for "group names" in part of my application,
and some of my group names are the names of youth sports age groups like
"Boys 9-10" or "Girls 11-12".
I would like for a search for the terms "Boys", "Boys 9-10", "9", "1
Hello --
I'm getting some unexpected results with a NOT IN query -- this is on 8.2.5.
This is the query in question:
prod_2=> select id from feed_download_task where id in (02466,141701504)
and id not in (select last_feed_download_task_id from subscription);
id
(0 rows)
This query re
Thanks -- that was it -- last_feed_download_task_id can indeed be null.
- Mason
On Thu, Mar 20, 2008 at 10:17 AM, Stephan Szabo <
[EMAIL PROTECTED]> wrote:
> On Thu, 20 Mar 2008, Mason Hale wrote:
>
> > Hello --
> >
> > I'm getting some unexpected result
I've been working on partitioning a rather large dataset into multiple
tables. One limitation I've run into the lack of cross-partition-table
unique indexes. In my case I need to guarantee the uniqueness of a
two-column pair across all partitions -- and this value is not used to
partition the table
On Tue, Jun 3, 2008 at 12:04 PM, Jeff Davis <[EMAIL PROTECTED]> wrote:
> On Tue, 2008-06-03 at 09:43 -0500, Mason Hale wrote:
>> I've been working on partitioning a rather large dataset into multiple
>> tables. One limitation I've run into the lack of cross-partition
Hello --
I'm seeing some odd warning in my postgres (8.3.6) logs.
2009-06-01 20:01:59 UTC (10.11.199.136)LOG: process 7070 still
waiting for ExclusiveLock on extension of relation 43911 of database
43623 after 1001.240 ms
2009-06-01 20:01:59 UTC (10.11.199.136)LOG: process 7070 acquired
Exclusi
Vacuum operations on several tables are taking much longer than they
previously were.We currently have 3 autovacuum processes that have been
running more than 3 days each.
The tables are large (between 40 and 90GB each).
Postgresql version is 8.3.1
maintenance_work_mem is 512MB (on a 32GB server
Marlowe <[EMAIL PROTECTED]>
wrote:
> On Mon, Jul 14, 2008 at 3:08 PM, Mason Hale <[EMAIL PROTECTED]> wrote:
> > Vacuum operations on several tables are taking much longer than they
> > previously were.
> > We currently have 3 autovacuum processes that have been ru
0}) = 0 (Timeout)" commands
sure look suspicious.
Any thoughts?
Mason
On Tue, Jul 15, 2008 at 10:06 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
> "Mason Hale" <[EMAIL PROTECTED]> writes:
> > The longest running vacuum has been running more than 6 days at this
> p
On Wed, Jul 16, 2008 at 10:26 AM, Mason Hale <[EMAIL PROTECTED]> wrote:
> Here's some of the strace output:
> select(0, NULL, NULL, NULL, {0, 1}) = 0 (Timeout)
>
If I read the 'select(2)' man page correctly, it appears this process is
waiting indefinitely fo
> On Wed, Jul 16, 2008 at 10:26 AM, Mason Hale <[EMAIL PROTECTED]> wrote:
>> Here's some of the strace output:
>>
>> select(0, NULL, NULL, NULL, {0, 1}) = 0 (Timeout)
>>
>> If I read the 'select(2)' man page correctly, it appears t
We have a table "entry" that is partitioned into two sub-tables named
"entry_part_new" and "entry_part_old", as described here:
http://www.postgresql.org/docs/8.3/interactive/ddl-partitioning.html
We find that in some cases, when joining to a partitioned table, the planner
makes wilding inaccurate
Hello --
I'm having a problem loading a recent pg_dump of our production database.
In our environment we take a monthly snapshot of our production server and
copy that to our development server so that we have a recent batch of data
to work with.
However, when trying to load the file for this m
like something is wrong with the copy command when loading the blocked_info
table. However when trying to load the pg_dump -Fc binary format export, it
appears there's some problem with the rawfeed table.
Any ideas on what to try next will be greatly appreciated.
thanks in advance,
Mason
On
I was able to successfully able to dump and restore my database this
morning.
Here's what I did:
After doing single table restore to a text file of the rawfeed table (the
one the triggered the error),
I was able to get the id of the last row that was successfully exported. As
mentioned earlier I
From what I've read about postgres 8.2, upgrading from any previous
version requires a full database dump and restore.
I am working with largish database (~70GB) that I would like to upgrade to 8.2.
A full database dump currently takes ~3 hrs, I expect a restore
require a similar timeframe if no
Hello -
After running pg_dump to backup my database, and then running pg_restore to
load the db (on a different server),
I run into a problem with creating a sequence. After the pg_restore is
completed, the sequence is created, but
the value is not set correctly. As a result calls nextval for the
On 4/14/07, Tom Lane <[EMAIL PROTECTED]> wrote:
Hmm ... which sequence is entry.id actually referring to on the source
database? I suspect that it is linked to some differently-named
sequence like "entry_id_seq1" and the source's "entry_id_seq" is not
in truth doing anything (and, in particular
31 matches
Mail list logo
