Hi All;
We have a client that has a table where large blobs (bytea) are stored.
the table has a key column that is numbers (like 112362) but
unfortunately it's a varchar column so the blobs are accessed via
queries like:
select * from bigtable where keycol = '217765'
The primary reason we
Hi All;
I have a query that wants to update a table based on a join like this:
update test_one
set f_key = t.f_key
from
upd_temp1 t,
test_one t2
where
t.id_number = t2.id_number
upd_temp1 has 248,762 rows
test_one has 248,762 rows
test_one has an index on f_key and an index on
On Monday 30 August 2010 17:04, bricklen wrote:
On Mon, Aug 30, 2010 at 3:28 PM, Kevin Kempter
cs_...@consistentstate.com wrote:
Hi all ;
we have an automated partition creation process that includes the
creation of an FK constraint. we have a few other servers with similar
scenarios
Hi all ;
we have an automated partition creation process that includes the creation of
an FK constraint. we have a few other servers with similar scenarios and this
is the only server that stinks per when we create the new partitions.
Anyone have any thoughts on how to debug this? were
Hi All;
we have a table partitioned by day, the check constraint on the child tables
looks like this (this is the may 31st partition):
CHECK
(stime = '2010-05-30 00:00:00+00'::timestamp with time zone
AND stime = '2010-05-30 23:59:59+00'::timestamp with time zone)
We have a python based
Hi all;
we've found that partition elimination is not happening for a prepared
statement, however running the same statement in psql manually does give us
partition elimination.
Is this a known issue?
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make
Hi All;
I have a table that has daily partitions.
The check constraints look like this:
CHECK (timezone('EST'::text, insert_dt) = '2010-01-01'::date
AND timezone('EST'::text, insert_dt) '2010-01-02'::date)
each partition has this index:
fact_idx1_20100101_on_cust_id btree (cust_id)
If I
On Wednesday 24 February 2010 07:55:36 A. Kretschmer wrote:
In response to Kevin Kempter :
Hi All;
I have a table that has daily partitions.
The check constraints look like this:
CHECK (timezone('EST'::text, insert_dt) = '2010-01-01'::date
AND timezone('EST'::text, insert_dt) '2010
On Thursday 07 January 2010 09:57:59 Kevin Grittner wrote:
Ludwik Dylag ldy...@gmail.com wrote:
I would suggest:
1. turn off autovacuum
1a. ewentually tune db for better performace for this kind of
operation (cant not help here)
2. restart database
3. drop all indexes
4. update
On Wednesday 25 November 2009 05:34:26 Richard Neill wrote:
Dear All,
Thanks very much for your help so far. My understanding of PG is getting
a lot better!
I wonder if I've understood analyze properly: I'm not sure I quite
understand how specific the statistics gathered actually are.
:22 -0400, Heikki Linnakangas
heikki.linnakan...@enterprisedb.com wrote:
Kevin Kempter wrote:
Hi all I have a large table (2billion rows) that's partitioned by date
based
on an epoch int value. We're running a select max(id) where id is the
PK. I
have a PK index on each
Hi all I have a large table (2billion rows) that's partitioned by date based
on an epoch int value. We're running a select max(id) where id is the PK. I
have a PK index on each of the partitions, no indexes at all on the base
table.
If I hit a partition table directly I get an index scan as
Hi all;
I cant figure out why we're scanning all of our partitions.
We setup our tables like this:
Base Table:
CREATE TABLE url_hits (
id integer NOT NULL,
content_type_id integer,
file_extension_id integer,
time integer,
bytes integer NOT NULL,
path_id integer,
On Wednesday 02 September 2009 09:02:27 Scott Marlowe wrote:
On Wed, Sep 2, 2009 at 8:52 AM, Kevin Kempterkev...@consistentstate.com
wrote:
Hi all;
I cant figure out why we're scanning all of our partitions.
We setup our tables like this:
Base Table:
CREATE TABLE url_hits (
On Wednesday 02 September 2009 08:55:38 Kenneth Marshall wrote:
The planner does not yet work as efficiently as it could
with child tables. Check the recent mail archives for a
long discussion of the same.
Regards,
Ken
On Wed, Sep 02, 2009 at 08:52:30AM -0600, Kevin Kempter wrote:
Hi all
Hi all;
We have a table that's 2billion rows big and growing fast. We've setup
monthly partitions for it. Upon running the first of many select * from
bigTable insert into partition statements (330million rows per month) the
entire box eventually goes out to lunch.
Any thoughts/suggestions?
On Tuesday 01 September 2009 03:26:08 Pierre Frédéric Caillaud wrote:
We have a table that's 2billion rows big and growing fast. We've setup
monthly partitions for it. Upon running the first of many select * from
bigTable insert into partition statements (330million rows per month) the
Hi all;
I have a simple query against two very large tables ( 800million rows in
theurl_hits_category_jt table and 9.2 million in the url_hits_klk1 table )
I have indexes on the join columns and I've run an explain.
also I've set the default statistics to 250 for both join columns. I get a
Hi all;
we've been fighting this query for a few days now. we bumped up the statistict
target for the a.id , c.url_hits_id and the b.id columns below to 250 and ran
an analyze on the relevant tables. we killed it after 8hrs.
Note the url_hits table has 1.4billion rows
Any suggestions?
On Wednesday 19 August 2009 11:17:26 Scott Carey wrote:
On 8/19/09 9:28 AM, Kevin Kempter kev...@consistentstate.com wrote:
Hi all;
we've been fighting this query for a few days now. we bumped up the
statistict target for the a.id , c.url_hits_id and the b.id columns below
to 250
On Wednesday 19 August 2009 11:31:30 Nikolas Everett wrote:
2009/8/19 Grzegorz Jaśkiewicz gryz...@gmail.com
that seems to be the killer:
and time = extract ('epoch' from timestamp '2009-08-12')
and time extract ('epoch' from timestamp '2009-08-13' )
You probably need an index on
Hi All;
I'm looking for tips / ideas per performance tuning some specific queries.
These are generally large tables on a highly active OLTP system
(100,000 - 200,000 plus queries per day)
First off, any thoughts per tuning inserts into large tables. I have a large
table with an insert like
Hi All;
I've installed pg_buffercache and I want to use it to help define the optimal
shared_buffers size.
Currently I run this each 15min via cron:
insert into buffercache_stats select now(), isdirty, count(*) as buffers,
(count(*) * 8192) as memory from pg_buffercache group by 1,2;
and
Hi List;
Anyone have any experiences to share per setting up a federated
architecture with PostgreSQL ? I wonder if the dblink contrib works
well in a federated scenario, specifically in the setup of the
federated views which equate to a select * from the same table on each
federated
Hi all;
I have a query that does this:
update tab_x set (inactive_dt, last_update_dt) =
((select run_dt from current_run_date), (select run_dt from
current_run_date))
where
cust_id::text || loc_id::text in
(select cust_id::text || loc_id::text from summary_tab);
The current_run_date table
Hi all;
I have 2 tables where I basically want to delete from the first table
(seg_id_tmp7) any rows where the entire row already exists in the
second table (sl_cd_segment_dim)
I have a query that looks like this (and it's slow):
delete from seg_id_tmp7
where
Version 8.3.1
On May 20, 2008, at 1:51 PM, kevin kempter wrote:
Hi all;
I have 2 tables where I basically want to delete from the first
table (seg_id_tmp7) any rows where the entire row already exists in
the second table (sl_cd_segment_dim)
I have a query that looks like
Hi List;
I have a table with 9,961,914 rows in it (see the describe of
bigtab_stats_fact_tmp14 below)
I also have a table with 7,785 rows in it (see the describe of
xsegment_dim below)
I'm running the join shown below and it takes 10 hours and
eventually runs out of disk space on a
Also, I'm running version 8.3 on a centOS box with 2 dual core CPU's
and 32Gig of ram
On May 16, 2008, at 12:58 AM, kevin kempter wrote:
Sorry I goofed on the query text Here's the correct query:
select
f14.xpublisher_dim_id,
f14.xtime_dim_id,
f14.xlocation_dim_id,
f14.xreferrer_dim_id
= segdim.episode_srcid
and segdim.segment_srcid is NULL;
On May 16, 2008, at 12:31 AM, kevin kempter wrote:
Hi List;
I have a table with 9,961,914 rows in it (see the describe of
bigtab_stats_fact_tmp14 below)
I also have a table with 7,785 rows in it (see the describe of
xsegment_dim below
product join.
On May 16, 2008, at 1:40 AM, Richard Huxton wrote:
kevin kempter wrote:
Hi List;
I have a table with 9,961,914 rows in it (see the describe of
bigtab_stats_fact_tmp14 below)
I also have a table with 7,785 rows in it (see the describe of
xsegment_dim below)
I'm running the join
wrote:
On Fri, 2008-05-16 at 00:31 -0600, kevin kempter wrote:
I'm running the join shown below and it takes 10 hours and
eventually runs out of disk space on a 1.4TB file system
Well, running in 10 hours doesn't mean there's a software problem, nor
does running out of disk space.
Please
Hi List;
I have a large tble (playback_device) with 6million rows in it. The
aff_id_tmp1 table has 600,000 rows.
I also have this query:
select distinct
tmp1.affiliate_id,
tmp1.name,
tmp1.description,
tmp1.create_dt,
tmp1.playback_device_id,
pf.segment_id
from
aff_id_tmp1 tmp1,
Hi List;
I'm having some performance issues with a partitioned table. We have a
VERY large table that we've partitioned by day.
Currently we have 17 partitions - each partition table contains
700million rows.
One of the things we need to query is the min date from the master
table - we
Hi List;
Sorry if this is a dupe, my first post never showed up...
I'm having some performance issues with a partitioned table. We have a
VERY large table that we've partitioned by day.
Currently we have 17 partitions - each partition table contains
700million rows.
One of the things we
On Tuesday 27 November 2007 09:33:36 cluster wrote:
I have a query that takes about 7000 ms in average to complete the first
time it runs. Subsequent runs complete in only 50 ms. That is more than
a factor 100 faster! How can I make the query perform good in the first
run too?
Query and
Hi List;
I'm preparing to create a test suite of very complex queries that can be
profiled in terms of load and performance. The ultimate goal is to define a
load/performance profile during a run of the old application code base and
then again with changes to the application code base.
I
Hi List;
any suggestions for improving LIKE '%text%' queries?
Thanks in advance
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Hi All;
I'm preparing to fire up WAL archiving on 8 production servers We will follow
up with implementing a warm standby scenariio.
Does anyone have any thoughts per how to maximize performance, yet minimize
the potential for data loss assuming we were not able to retrieve the final
Hi List;
I've recently started cleaning up some postgres db's which previous to my
recent arrival had no DBA to care for them.
I quickly figured out that there were several tables which were grossly full
of dead space. One table in particular had 75G worth of dead pages (or the
equivelant in
On Monday 27 August 2007 15:56:33 Kevin Grittner wrote:
Decibel! [EMAIL PROTECTED] 08/27/07 4:00 PM
They're running version 8.1.4
As for your pg_dump idea... why not just do a CREATE TABLE AS SELECT *
FROM bloated_table? That would likely be much faster than messing around
with
On Monday 27 August 2007 15:00:41 you wrote:
On Fri, Aug 24, 2007 at 04:41:44PM -0400, Bill Moran wrote:
In response to Kevin Kempter [EMAIL PROTECTED]:
Hi List;
I've just started working with a client that has been running Postgres
(with no DBA) for a few years. They're running
On Monday 27 August 2007 16:04:39 Decibel! wrote:
On Mon, Aug 27, 2007 at 04:56:33PM -0500, Kevin Grittner wrote:
Decibel! [EMAIL PROTECTED] 08/27/07 4:00 PM
They're running version 8.1.4
As for your pg_dump idea... why not just do a CREATE TABLE AS SELECT *
FROM
Hi List;
I've just inherited multiple postgres database servers in multiple data
centers across the US and Europe via a new contract I've just started.
Each night during the nightly batch processing several of the servers (2 in
particular) slow to a crawl - they are dedicated postgres database
Hi List;
I've just started working with a client that has been running Postgres (with
no DBA) for a few years. They're running version 8.1.4 on 4-way dell boxes
with 4Gig of memory on each box attached to RAID-10 disk arrays.
Some of their key config settings are here:
shared_buffers = 20480
On Friday 24 August 2007 15:39:22 Tom Lane wrote:
Kevin Kempter [EMAIL PROTECTED] writes:
The development folks that have been here awhile tell me that it seems
like when they have a query (not limited to vacuum processes) that has
been running for a long time (i.e. 5 or 6 hours
Hi List;
I've just started working with a new client and they have amoung other issues
with their databases a particular update that basically locks out users.
The below query was running for over 6 hours this morning and the CPU load had
climbed to a point where new connections simply hung
Hi List(s);
I'm wanting to find more details per the following methods, can someone
explain to me exactly what each of these methods is, how its implemented in
postgres or point me to some docs or README's that explain these methods?
Some of 'em are obviously no-brainers but I'm writing a
Hi List;
I have a client looking to host/co-locate multiple PostgreSQL clusters
(inclusive of PL/pgSQL application code) per server. I did some co-location
work several years back with one of the bigger telco's and remember there
were dire consequences for not carefully evaluating the expected
49 matches
Mail list logo
