On Wed, Sep 12, 2012 at 7:42 PM, Scott Marlowe scott.marl...@gmail.comwrote:
On Wed, Sep 12, 2012 at 12:57 AM, Venkat Balaji venkat.bal...@verse.in
wrote:
We are using PostgreSQL-9.0.1.
You are missing almost 2 years of updates, bug fixes, and security fixes.
Thank you Scott, We
Thank you Jeff !
My comments are inline.
explain SELECT tv.short_code, tv.chn as pkg_subscription_chn,
tv.vert as pkg_vert, ubs.campaign_id as campaign,
'none'::varchar as referer,
CAST('CAMPAIGNWISE_SUBSCRIBER_BASE' AS VARCHAR) as vn,
count(tv.msisdn) as
Hello Community,
I intend to understand further on PostgreSQL Index behavior on a SELECT
statement.
We have a situation where-in Index on unique column is not being picked up
as expected when used with-in the WHERE clause with other non-unique
columns using AND operator.
explain SELECT
The cost is 13.88 to fetch 1 row by scanning an Primary Key
indexed column.
Isn't the cost for fetching 1 row is too high ?
I don't know, how many index pages will need to be randomly accessed
in addition to the random heap access? How many dead versions of
the row will need to be
Thanks for your valuable inputs !
The cost is 13.88 to fetch 1 row by scanning an Primary Key
indexed column.
Isn't the cost for fetching 1 row is too high ?
Not really. The cost is really just an estimate to rank alternate query
plans so the database picks the least expensive plan. The
Hello,
I am trying to understand the analysis behind the cost attribute in
EXPLAIN output.
postgres = # explain select * from table_event where seq_id=8520960;
QUERY PLAN
On Wed, Nov 9, 2011 at 8:16 PM, Scott Marlowe scott.marl...@gmail.comwrote:
On Wed, Nov 9, 2011 at 2:25 AM, Venkat Balaji venkat.bal...@verse.in
wrote:
Hello Everyone,
I could see the following in the production server (result of the top M
command) -
PIDUSER PR NI VIRTRES
Hello Everyone,
I could see the following in the production server (result of the top M
command) -
PIDUSER PR NI VIRTRES SHR S %CPU %MEM TIME+
COMMAND
25265 postgres 15 0 3329m 2.5g 1.9g S 0.0 4.0
542:47.83 postgres: writer process
The
Thanks Greg !
Sorry for delayed response.
We are actually waiting to change the checkpoint_segments in our production
systems (waiting for the downtime).
Thanks
VB
On Wed, Oct 5, 2011 at 11:02 AM, Greg Smith g...@2ndquadrant.com wrote:
On 10/04/2011 07:50 PM, Venkat Balaji wrote:
I
Oh yes.
Thanks a lot Robert !
Regards
VB
On Tue, Oct 25, 2011 at 7:47 AM, Robert Haas robertmh...@gmail.com wrote:
On Oct 24, 2011, at 8:16 AM, Venkat Balaji venkat.bal...@verse.in wrote:
Thanks Greg !
Sorry for delayed response.
We are actually waiting to change
after considering Vacuum and Analyze jobs are
executed.
Please comment !
Sorry if this is too confusing and too long.
Thanks
VB
On Wed, Sep 21, 2011 at 6:33 PM, Shaun Thomas stho...@peak6.com wrote:
On 09/20/2011 11:22 AM, Venkat Balaji wrote:
Please help me understand how to calculate free
Hello Everyone,
Generally when it comes to query performance, I check how the vacuuming and
statistics collection is performed on Tables and Indexes hit by the query.
Apart from the above i check the code logic ( for any bad joins ) and column
statistics as well.
I got hold of two catalog
bgwriter_delay = 200ms
bgwriter_lru_maxpages = 100
bgwriter_lru_multiplier = 2
Looking forward for suggestions.
Thanks
VB
On Thu, Sep 29, 2011 at 12:40 PM, Venkat Balaji venkat.bal...@verse.inwrote:
Hello Everyone,
We are experience a huge drop in performance for one of our production
Thanks Heikki !
Regards,
VB
On Tue, Oct 4, 2011 at 4:38 PM, Heikki Linnakangas
heikki.linnakan...@enterprisedb.com wrote:
On 04.10.2011 13:50, Venkat Balaji wrote:
I have got a situation where in i see the production system is loaded with
the checkpoints and at-least 1000+ buffers
5, 2011 at 4:02 AM, Greg Smith g...@2ndquadrant.com wrote:
On 10/04/2011 03:50 AM, Venkat Balaji wrote:
I had a look at the pg_stat_bgwriter as well.
Try saving it like this instead:
select now(),* from pg_stat_bgwriter;
And collect two data points, space a day or more apart. That gives
Hello,
Thanks for your suggestions !
We CLUSTERED a table using mostly used Index. Application is performing
better now.
Thanks
VB
On Tue, Sep 27, 2011 at 6:01 PM, Venkat Balaji venkat.bal...@verse.inwrote:
Forgot to mention -
Kevin,
CLUSTER seems to be an very interesting concept to me
according to the type of IOs.
I will share my recommended plan in an different email thread.
Thanks again for this detailed explanation.
Regards,
VB
On Mon, Oct 3, 2011 at 9:45 PM, Kevin Grittner
kevin.gritt...@wicourts.govwrote:
Venkat Balaji venkat.bal...@verse.in wrote:
We CLUSTERED a table
:
Venkat Balaji wrote:
1. Big Full Table Scans
2. Table with high IOs (hot tables)
3. Highly used Indexes
4. Tables undergoing high DMLs with index scans 0 (with unused indexes)
5. Index usage for heap blk hits
6. Tracking Checkpoints
This is fairly easy to collect and analyze. You might take
Hi Tomas,
I will let you know about check_postgres.pl.
We will explore pgmonitor as well.
The other tool we are working on is pgwatch, we found this very useful.
Thanks
VB
On Wed, Sep 28, 2011 at 5:44 PM, Tomas Vondra t...@fuzzy.cz wrote:
On 28 Září 2011, 9:05, Greg Smith wrote:
Venkat
CPU load was hitting 100% constantly with high IOs.
We tuned some queries to decrease the CPU usage and everything is normal
now.
Thanks
VB
On Fri, Sep 30, 2011 at 10:52 AM, Venkat Balaji venkat.bal...@verse.inwrote:
I did not calculate the IO behavior of the server.
What i noticed
, Venkat Balaji venkat.bal...@verse.in
wrote:
Hello Everyone,
I am back with an issue (likely).
I am trying to create a table in our production database, and is taking 5
seconds.
We have executed VACUUM FULL and yet to run ANALYZE. Can i expect the
CREATE
TABLE to be faster after ANALYZE
intensive.
Thanks
VB
On Thu, Sep 29, 2011 at 10:22 PM, Merlin Moncure mmonc...@gmail.com wrote:
On Wed, Sep 28, 2011 at 12:06 PM, Venkat Balaji venkat.bal...@verse.in
wrote:
Hello Everyone,
I am back with an issue (likely).
I am trying to create a table in our production database, and is taking
Thanks Kevin !!
I will have a look at the source tree.
Regards
VB
On Tue, Sep 27, 2011 at 10:45 PM, Kevin Grittner
kevin.gritt...@wicourts.gov wrote:
Venkat Balaji venkat.bal...@verse.in wrote:
I would like to know the difference between n_tup_upd and
n_tup_hot_upd.
A HOT update
for this in Postgres. I think we can get
this using pg_stat_user_table, pg_statio_user_tables and pg_stats.
I will post the calculation once it i get it.
Thanks
VB
On Wed, Sep 28, 2011 at 6:25 AM, Craig Ringer ring...@ringerc.id.au wrote:
On 09/28/2011 12:26 AM, Venkat Balaji wrote:
Thanks a lot
Hello Everyone,
I am back with an issue (likely).
I am trying to create a table in our production database, and is taking 5
seconds.
We have executed VACUUM FULL and yet to run ANALYZE. Can i expect the CREATE
TABLE to be faster after ANALYZE finishes ?
Or is there anything serious ?
Please
Hello Everyone,
I have been working on PostgreSQL for quite a while (2 yrs) now.
I have got PostgreSQL 9.0 High Performance book today and quite excited to
go through it.
Please let me know any source where i can get more books on PG, I am
especially looking for books on PG internals,
, Kevin Grittner
kevin.gritt...@wicourts.gov wrote:
Venkat Balaji venkat.bal...@verse.in 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
Forgot to mention -
Kevin,
CLUSTER seems to be an very interesting concept to me.
I am thinking to test the CLUSTER TABLE on our production according to the
Index usage on the table.
Will let you know once i get the results.
Regards,
VB
On Tue, Sep 27, 2011 at 5:59 PM, Venkat Balaji
Hello Everyone,
I am preparing a plan to track the tables undergoing Full Table Scans for
most number of times.
If i track seq_scan from the pg_stat_user_tables, will that help
(considering the latest analyzed ones) ?
Please help !
Thanks
VB
at 8:02 PM, Kevin Grittner kevin.gritt...@wicourts.gov
wrote:
Venkat Balaji venkat.bal...@verse.in wrote:
I am preparing a plan to track the tables undergoing Full Table
Scans for most number of times.
If i track seq_scan from the pg_stat_user_tables, will that help
(considering
Hello Everyone,
I am implementing a PostgreSQL performance monitoring system (to monitor the
below) which would help us understand the database behavior -
1. Big Full Table Scans
2. Table with high IOs (hot tables)
3. Highly used Indexes
4. Tables undergoing high DMLs with index scans 0 (with
I would like to know the difference between n_tup_upd and n_tup_hot_upd.
Thanks
VB
On Tue, Sep 27, 2011 at 9:56 PM, Venkat Balaji venkat.bal...@verse.inwrote:
Thanks a lot Kevin !!
Yes. I intended to track full table scans first to ensure that only small
tables or tables with very less
Can you please help me understand what blkno column refers to ?
Thanks
Venkat
On Wed, Sep 21, 2011 at 11:08 AM, Venkat Balaji venkat.bal...@verse.inwrote:
Thank Everyone for your inputs !
Mark,
We are using 9.0, so, i should be able to make use of this freespacemap
contrib module
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 rumman...@gmail.com wrote:
I
for the long email !
Looking forward for your help !
Thanks
Venkat
On Wed, Sep 21, 2011 at 7:27 PM, Kevin Grittner kevin.gritt...@wicourts.gov
wrote:
Shaun Thomas stho...@peak6.com wrote:
Venkat Balaji wrote:
I see lot of free spaces or free pages in Tables and Indexes.
But, I need
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 stho...@peak6.com wrote:
On 09/21/2011
it correct !
Thanks
Venkat
On Wed, Sep 21, 2011 at 11:27 PM, Greg Smith g...@2ndquadrant.com wrote:
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
Hello Everyone,
I had posted a query in GENERAL category, not sure if that was the
correct category to post.
Please help me understand how to calculate free space in Tables and Indexes
even after vacuuming and analyzing is performed.
What i understand is that, even if we perform VACUUM
Thank Everyone for your inputs !
Mark,
We are using 9.0, so, i should be able to make use of this freespacemap
contrib module and would get back to you with the results.
I was using below query (which i got it by googling)..
But, was not sure, if its picking up the correct information. I want
scott.marl...@gmail.comwrote:
On Thu, Sep 1, 2011 at 11:46 PM, Venkat Balaji venkat.bal...@verse.in
wrote:
Hi Scott,
Log generation rate -
500MB size of log file is generated within minimum 3 mins to maximum of
20
mins depending on the database behavior.
I did not understand the fsync stuff
file ?
Thanks
Venkat
On Tue, Aug 30, 2011 at 12:09 PM, Scott Marlowe scott.marl...@gmail.comwrote:
On Mon, Aug 29, 2011 at 11:55 PM, Venkat Balaji venkat.bal...@verse.in
wrote:
If i notice high IO's and huge log generation, then i think Greg
Spileburg
has suggested a good idea of using
Missed out looping in community...
On Wed, Aug 31, 2011 at 5:01 PM, Venkat Balaji venkat.bal...@verse.inwrote:
Could you help us know the tables and columns on which Indexes are built ?
Query is performing sorting based on key upper(column) and that is where i
believe the cost is high
Thanks to all for your very helpful replies !
As Greg Smith rightly said, i faced a problem of missing connections between
the runs. I even ran the cron every less than a second, but, still that
would become too many runs per second and later i need to take the burden of
calculating every thing
Hello Everyone,
I am working on an alert script to track the number of connections with the
host IPs to the Postgres cluster.
1. I need all the host IPs making a connection to Postgres Cluster (even for
a fraction of second).
2. I would also want to track number of IDLE connections, IDLE IN
to catalog tables ).
Thanks
Venkat
On Wed, Aug 24, 2011 at 1:19 PM, Guillaume Lelarge
guilla...@lelarge.infowrote:
On Wed, 2011-08-24 at 13:05 +0530, Venkat Balaji wrote:
Hello Everyone,
I am working on an alert script to track the number of connections with
the
host IPs to the Postgres
,
but, information logged would be too high and is also IO intensive.
Thanks
Venkat
On Wed, Aug 24, 2011 at 4:39 PM, Adarsh Sharma adarsh.sha...@orkash.comwrote:
**
pg_stat_activity keeps track of all this information.
select * from pg_stat_activity where datname='databasename';
Venkat Balaji
46 matches
Mail list logo
