Michael Viscuso writes:
> Greg/Tom, you are correct, these columns should be modified to whatever
> is easiest for Postgres to recognize 64-bit unsigned integers. Would
> you still recommend bigint for unsigned integers? I likely read the
> wrong documentation that suggested bigint for signed 64
Thanks guys,
First of all, I should have included my postgres.conf file with the
original submission. Sorry about that. It is now attached.
Based on a recommendation, I also should have shown the parent child
relationship between osmoduleloads and its daily partitioned tables. to
reduce clutte
Greg Smith writes:
> That weird casting can't be helping. I'm not sure if it's your problem
> here, but the constraint exclusion code is pretty picky about matching
> the thing you're looking for against the CHECK constraint, and this is a
> messy one. The bigint conversion in the middle ther
On 09/21/2011 07:14 PM, Michael Viscuso wrote:
Check constraints:
"osmoduleloads_2011_09_14_event_time_check" CHECK (event_time =
'2011-09-14 00:00:00'::timestamp without time zone)
"osmoduleloads_2011_09_14_firstloadtime_check" CHECK
(firstloadtime >= 1296044640::bigint::numeri
First of all, thank you for taking the time to review my question. After
attending the PostgresOpen conference in Chicago last week, I've been
pouring over explain logs for hours on end and although my system is MUCH
better, I still can't resolve a few issues. Luckily my data is pretty well
struc
Sorry all - this was a duplicate from another of my addresses =( Thanks to all
that have helped out on both threads.
On 21/09/2011, at 8:44 AM, Royce Ausburn wrote:
> Hi all,
>
> It looks like I've been hit with this well known issue. I have a complicated
> query that is intended to run
Venkat Balaji wrote:
> If i got it correct, CLUSTER would do the same what VACUUM FULL
> does (except being fast)
CLUSTER copies the table (in the sequence of the specified index) to
a new set of files, builds fresh indexes, and then replaces the
original set of files with the new ones. So yo
Thanks Greg !
If i got it correct, CLUSTER would do the same what VACUUM FULL does (except
being fast).
CLUSTER is recommended only because it is faster ? As per the link, the
table would be unavailable (for shorter period compared to VACUUM FULL) when
CLUSTER is executed as well. Hope i got it c
On 09/21/2011 12:13 PM, Venkat Balaji wrote:
I as a DBA, suggested to perform VACUUM FULL and RE-INDEXING + ANALYZE
to ensure that IO performance and Indexing performance would be good
Read http://wiki.postgresql.org/wiki/VACUUM_FULL before you run VACUUM
FULL. You probably don't want to do
=?ISO-8859-1?Q?Gunnlaugur_=DE=F3r_Briem?= writes:
> On Monday, September 19, 2011 3:59:30 AM UTC, Tom Lane wrote:
>> Works for me in 8.4.8. Do you have constraint_exclusion set to ON?
> I did try with constraint_exclusion set to on, though the docs suggest
> partition should be enough ("examine
On Tue, Sep 20, 2011 at 5:44 PM, Royce Ausburn wrote:
> Hi all,
> It looks like I've been hit with this well known issue. I have
> a complicated query that is intended to run every few minutes, I'm using
> JDBC's Connection.prepareStatement() mostly for nice parameterisation, but
> postgres produ
On 09/21/2011 11:20 AM, Venkat Balaji wrote:
It is very important to remove it from the WIKI page.
Removing it is a little premature. :) Definitely going to add a warning
about relying on its output, though. The query itself was created and
integrated into the check_postgres.pl nagios plugin
It is very important to remove it from the WIKI page.
I ran it on production PG9.0 and it does not error out and displays numbered
output.
I noticed that, this works till PG-8.2 (as per the message).
Venkat
On Wed, Sep 21, 2011 at 8:25 PM, Shaun Thomas wrote:
> On 09/21/2011 09:12 AM, Tom Lan
Thank you very much for your detailed explanation !
I will be working on our existing "auto-vacuuming" strategy to see
if that's optimal. But, we do have VACUUM VERBOSE ANALYZE running at the
cluster level every day and auto-vacuum is aggressive for highly active
tables.
Today, we have vacuumed a
On 09/21/2011 08:43 AM, Guillaume Cottenceau wrote:
AI Rumman writes:
Using the query http://wiki.postgresql.org/wiki/Show_database_bloat, I got the
Is this stuff to show database bloat reliable?
Only in that increase and decreases of the number reported can be useful
for determi
Thomas Kappler writes:
> The query we want to do is (with example values):
> select t.cid, count(distinct t1.subject_id)
> from termindex t1, termindex t2
> where t1.cid=20642 and t1.indextype=636 and t2.indextype=636 and
> t2.subject_id=t1.subject_id
> group by t2.cid;
The EXPLAIN output you pr
On 09/21/2011 09:12 AM, Tom Lane wrote:
The PG wiki is editable by anyone who signs up for an account. Feel
free to put in an appropriate disclaimer, or improve the sample
query.
Ah, well then. I do have an account, but thought there were more
granular page restrictions than that. I may have
On Tue, Sep 20, 2011 at 7:43 PM, Thomas Kappler wrote:
> [please CC, I'm not on the list]
>
> Hi all,
>
> we have one table that basically uses Postgres as a key-value store.
>
> Table "public.termindex"
> Column | Type | Modifiers
> -+-+---
> subject_id | int
one thing, in SUM() , you don't have to coalesce. Consider following example:
foo=# create table bar(id serial primary key, a float);
NOTICE: CREATE TABLE will create implicit sequence "bar_id_seq" for
serial column "bar.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"bar_pkey
On Monday, September 19, 2011 3:59:30 AM UTC, Tom Lane wrote:
> Works for me in 8.4.8. Do you have constraint_exclusion set to ON?
I did try with constraint_exclusion set to on, though the docs suggest
partition should be enough ("examine constraints only for ... UNION ALL
subqueries")
Here's
[please CC, I'm not on the list]
Hi all,
we have one table that basically uses Postgres as a key-value store.
Table "public.termindex"
Column | Type | Modifiers
-+-+---
subject_id | integer |
indextype | integer |
cid| integer |
This is with Pos
Hi all,
It looks like I've been hit with this well known issue. I have a complicated
query that is intended to run every few minutes, I'm using JDBC's
Connection.prepareStatement() mostly for nice parameterisation, but postgres
produces a suboptimal plan due to its lack of information when the
2011/9/21 Hellmuth Vargas :
> SAN
> VMWare
> CentOS 6-64bits
> PostgreSQL 9-64bits
> Tengo virtualizada la maquina para atender otros servicios, entonces
> mi pregunta es.. los parámetros y consideraciones que establecen en el
> articulo son aplicables al esquema visualizado especialmente en cuanto
On Sep 21, 2011, at 8:30 AM, Shaun Thomas wrote:
> I wish they would erase that Wiki page, or at least add a disclaimer.
The "they" that you refer to includes you. It's a wiki. You can write your
own disclaimer.
xoa
--
Andy Lester => a...@petdance.com => www.petdance.com => AIM:petdance
Shaun Thomas writes:
> On 09/21/2011 02:01 AM, AI Rumman wrote:
>> Using the query http://wiki.postgresql.org/wiki/Show_database_bloat,
>> I got the following result for a table:
> God. I wish they would erase that Wiki page, or at least add a
> disclaimer. That query is no better than a loose e
Shaun Thomas wrote:
> Venkat Balaji wrote:
>> I see lot of free spaces or free pages in Tables and Indexes.
>> But, I need to give an exact calculation on how much space will
>> be reclaimed after VACUUM FULL and RE-INDEXING.
>
> Why?
I've been wondering that, too. And talking about the spa
buen dia
a partir de los siguientes artículos publicados en la primera edicion de
PostgreSQL Magazine (http://pgmag.org/00/read) (Performance Tunning
PostgreSQL y Tuning linux for PostgreSQL) me dispuse a implementar los mismo
en mi servidor de Postgres pero cuento con la siguiente arquitectura
S
On 09/21/2011 02:01 AM, AI Rumman wrote:
Using the query http://wiki.postgresql.org/wiki/Show_database_bloat,
I got the following result for a table:
God. I wish they would erase that Wiki page, or at least add a
disclaimer. That query is no better than a loose estimate. Never, ever,
ever de
On 09/20/2011 11:22 AM, Venkat Balaji wrote:
Please help me understand how to calculate free space in Tables and
Indexes even after vacuuming and analyzing is performed.
Besides the query Mark gave you using freespacemap, there's also the
pgstattuple contrib module. You'd use it like this:
On 09/20/2011 11:29 PM, Hany ABOU-GHOURY wrote:
Thanks but...did not work different error though
ERROR: relation "history" already exists
ERROR: relation "idx_history_pagegroupid" already exists
ERROR: constraint "cdocumentid" for relation "history" already exists
Clearly the history table al
AI Rumman writes:
> Using the query http://wiki.postgresql.org/wiki/Show_database_bloat, I got the
Is this stuff to show database bloat reliable? After a VACUUM
FULL of the table reported as the top responsible of bloat,
performing the same request again still gives the same result
(still that t
On Wed, 2011-09-21 at 13:01 +0600, AI Rumman wrote:
> I am using Postgresql 9.0.1.
>
> I REINDEXED both the indexes and table, but I did not find any change
> in wastedspace or wastedispace.
> Could you please tell me why?
you need to
1) either vacuum full or cluster the table
2) analyze the
Yes I ANALYZE the table, but no change for wastedispace.
On Wed, Sep 21, 2011 at 1:06 PM, Guillaume Lelarge
wrote:
> On Wed, 2011-09-21 at 13:01 +0600, AI Rumman wrote:
> > I am using Postgresql 9.0.1.
> >
> > Using the query http://wiki.postgresql.org/wiki/Show_database_bloat, I
> got
> > the fo
Could you please let us know if you have analyzed after the re-indexing is
done ?
This must show differences for only Indexes not the Tables.
For Tables, you need to do VACUUM FULL to show the difference.
Thanks
Venkat
On Wed, Sep 21, 2011 at 12:31 PM, AI Rumman wrote:
> I am using Postgresql
On Wed, 2011-09-21 at 13:01 +0600, AI Rumman wrote:
> I am using Postgresql 9.0.1.
>
> Using the query http://wiki.postgresql.org/wiki/Show_database_bloat, I got
> the following result for a table:
>
> -[ RECORD 1 ]+---
> current_database | crm
> sc
I am using Postgresql 9.0.1.
Using the query http://wiki.postgresql.org/wiki/Show_database_bloat, I got
the following result for a table:
-[ RECORD 1 ]+---
current_database | crm
schemaname | public
tablename| _attachments
tbloat
36 matches
Mail list logo
