[PERFORM] unnecessary sort in the execution plan when doing group by

Hi, This is the Greenplum database 4.3.1.0. Tables : dev=# \d+ visits_weekly_new_3 Append-Only Columnar Table uk.visits_weekly_new_3 Column | Type | Modifiers | Storage | Compression Type | Compression Level | Block Size | Description

Re: [PERFORM] unnecessary sort in the execution plan when doing group by

Thank you Dave. I've opened an SR with GP and see if they have any good suggestion on changing the plan. Thanks, Suya From: David Rowley [dgrowle...@gmail.com] Sent: Tuesday, October 28, 2014 6:06 PM To: Huang, Suya Cc: pgsql-performance@postgresql.org Subject

[PERFORM] How to interpret view pg_stat_bgwriter

Hi, Sorry for send this email twice but it seems it fits the performance group than admin group... I was reading an article of Gregory Smith http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm and tried to do some analysis on our database. postgres=# select * from

Re: [PERFORM] weird execution plan

-Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of David G Johnston Sent: Saturday, September 13, 2014 7:34 AM To: pgsql-performance@postgresql.org Subject: Re: [PERFORM] weird execution plan Not everyone does so

[PERFORM] weird execution plan

Hi, Can someone figure out why the first query runs so slow comparing to the second one? They generate the same result... dev=# explain analyze select count(distinct wid) from terms_weekly_20140503 a join port_terms b on a.term=b.terms;

Re: [PERFORM] how to change the provoke table in hash join

From: Jeff Janes [mailto:jeff.ja...@gmail.com] Sent: Friday, September 12, 2014 4:09 AM To: Matheus de Oliveira Cc: Huang, Suya; pgsql-performance@postgresql.org Subject: Re: [PERFORM] how to change the provoke table in hash join On Thu, Sep 11, 2014 at 7:09 AM, Matheus de Oliveira matioli.math

Re: [PERFORM] weird execution plan

-Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of David G Johnston Sent: Friday, September 12, 2014 12:45 PM To: pgsql-performance@postgresql.org Subject: Re: [PERFORM] weird execution plan Huang, Suya wrote Can

[PERFORM] how to change the provoke table in hash join

Hi, Below are two query plan for same SQL with and without an index. I noticed the Hash join order has changed since index has been created and this is not what I want. As it's hashing the big table and to provoke records in a small table. in Oracle, it's simple to add hint to point the table

Re: [PERFORM] query performance with hstore vs. non-hstore

Subject: Re: [PERFORM] query performance with hstore vs. non-hstore Huang, Suya wrote See output of explain (analyze,timing off), the total runtime is close to the one enable timing. Calling 43s close to 70s doesn't sound right... dev=# explain (analyze, timing off) select cha_type, sum(visits

[PERFORM] query performance with hstore vs. non-hstore

Hi , I'm tweaking table layout to get better performance of query. One table doesn't use hstore but expand all metrics of cha_type to different rows. The other table has hstore for metrics column as cha_type-metrics so it has less records than the first one. I would be expecting the query on

Re: [PERFORM] query performance with hstore vs. non-hstore

, then there is benefit of Hstore. Last benefit of Hstore are indexes over tuple (key, value) .. but you don't use it. Regards Pavel 2014-09-01 8:10 GMT+02:00 Huang, Suya suya.hu...@au.experian.commailto:suya.hu...@au.experian.com: Hi , I’m tweaking table layout to get better performance

Re: [PERFORM] query performance with hstore vs. non-hstore

rows=371759 loops=1) Total runtime: 69521.570 ms (11 rows) Thanks, Suya From: Pavel Stehule [mailto:pavel.steh...@gmail.com] Sent: Monday, September 01, 2014 5:07 PM To: Huang, Suya Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] query performance with hstore vs. non-hstore 2014-09

[PERFORM] query on parent partition table has bad performance

Hi, I have a question about partition table query performance in postgresql, it's an old version 8.3.21, I know it's already out of support. so any words about the reason for the behavior would be very much appreciated. I have a partition table which name is test_rank_2014_monthly and it has 7

Re: [PERFORM] query on parent partition table has bad performance

-Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Thursday, August 21, 2014 12:13 AM To: Huang, Suya Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] query on parent partition table has bad performance Huang, Suya suya.hu...@au.experian.com writes: I have

[PERFORM] GIN index not used

Hi, I've got a table with GIN index on integer[] type. While doing a query with filter criteria on that column has GIN index created, it's not using index at all, still do the full table scan. Wondering why? Table is analyzed. dev=# \d+ booking_weekly Table

Re: [PERFORM] GIN index not used

-Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Friday, July 11, 2014 2:56 PM To: Andreas Kretschmer Cc: Huang, Suya; pgsql-performance@postgresql.org Subject: Re: [PERFORM] GIN index not used Andreas Kretschmer akretsch...@spamfence.net writes: Huang, Suya suya.hu

Re: [PERFORM] GIN index not used

-Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Friday, July 11, 2014 3:43 PM To: Huang, Suya Cc: Andreas Kretschmer; pgsql-performance@postgresql.org Subject: Re: [PERFORM] GIN index not used Huang, Suya suya.hu...@au.experian.com writes: Just found out something

[PERFORM] DB sessions 100 times of DB connections

Hi, We've experienced a DB issue yesterday and after checked the log found that the peak sessions is 3000 while the peak DB connections is only around 30. The application is having problem of pulling data but no warnings in DB log as it doesn't exceed max_connections. How could this happen?

Re: [PERFORM] DB sessions 100 times of DB connections

BTW, I'm using the pgbadger report to check for peak connections/sessions. From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Huang, Suya Sent: Friday, July 04, 2014 11:44 AM To: pgsql-performance@postgresql.org Subject: [PERFORM] DB

Re: [PERFORM] huge pgstat.stat file on PostgreSQL 8.3.24

-Original Message- From: Tomas Vondra [mailto:t...@fuzzy.cz] Sent: Friday, June 20, 2014 8:14 PM To: Pavel Stehule Cc: Huang, Suya; pgsql-performance@postgresql.org Subject: Re: [PERFORM] huge pgstat.stat file on PostgreSQL 8.3.24 On 20 Červen 2014, 5:33, Pavel Stehule wrote: 2014-06

Re: [PERFORM] huge pgstat.stat file on PostgreSQL 8.3.24

From: Pavel Stehule [mailto:pavel.steh...@gmail.com] Sent: Thursday, June 19, 2014 3:41 PM To: Huang, Suya Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] huge pgstat.stat file on PostgreSQL 8.3.24 2014-06-19 7:35 GMT+02:00 Huang, Suya suya.hu...@au.experian.com: From: Pavel

[PERFORM] huge pgstat.stat file on PostgreSQL 8.3.24

Hi group, We've found huge pgstat.stat file on our production DB boxes, the size is over 100MB. autovacuum is enabled. So my question would be: 1. What's a reasonable size of pgstat.stat file, can it be estimated? 2. What's the safest way to reduce the file size to alleviate the IO

Re: [PERFORM] huge pgstat.stat file on PostgreSQL 8.3.24

From: Pavel Stehule [mailto:pavel.steh...@gmail.com] Sent: Thursday, June 19, 2014 3:28 PM To: Huang, Suya Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] huge pgstat.stat file on PostgreSQL 8.3.24 Hello The size of statfile is related to size of database objects in database

[PERFORM] parse/bind/execute

Hello, I am using Pgbadger to analyze the postgresql database log recently and noticed a section Prepared queries ratio. For my report, it has: 1.03 as Ratio of bind vs prepare 0.12% Ratio between prepared and usual statements I'm trying to understand what the above metrics mean and if it's a

Re: [PERFORM] parse/bind/execute

: Thursday, June 05, 2014 11:58 AM To: pgsql-performance@postgresql.org Subject: Re: [PERFORM] parse/bind/execute Huang, Suya wrote Hello, I am using Pgbadger to analyze the postgresql database log recently and noticed a section Prepared queries ratio. For my report, it has: 1.03 as Ratio

Re: [PERFORM] same query different execution plan (hash join vs. semi-hash join)

Thanks Tom, I think you're right. I just did an analyze on table test1 and the execution plan now generated is more stable and predictable. Thanks, Suya -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Tuesday, May 20, 2014 12:22 AM To: Huang, Suya Cc: pgsql

Re: [PERFORM] same query different execution plan (hash join vs. semi-hash join)

Thank you Tom. But the time spent on scanning table test1 is less than 1 second (91.738 compares to 87.869), so I guess this shouldn't be the issue? -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Friday, May 16, 2014 12:58 PM To: Huang, Suya Cc: pgsql-performance

[PERFORM] same query different execution plan (hash join vs. semi-hash join)

Hi buddies, I've got a query as below, it runs several times with different execution plan and totally different execution time. The one using hash-join is slow and the one using semi-hash join is very fast. However, I have no control over the optimizer behavior of PostgreSQL database. Or, do

[PERFORM] how to speed up the index creation in GP?

Hi Guys, I'm new to Postgresql, we have a Greenplum cluster and need to create many indexes on the database. So my question is: Is there any performance tips for creating index on Postgres? how to monitor the progress the creation process? Thanks and best regards, Suya Huang