Re: Performance of a Query

2018-01-10 Thread Laurenz Albe 
Scott Marlowe wrote:
> On Tue, Jan 9, 2018 at 2:18 PM, Kumar, Virendra
>  wrote:
> > Hello Gurus,
> > 
> > I am struggling to tune a query which is doing join on top of aggregate for
> > around 3 million rows. The plan and SQL is attached to the email.
> > 
> > Below is system Details:
> > 
> > PGSQL version – 10.1
> > 
> > OS – RHEL 3.10.0-693.5.2.el7.x86_64
> > 
> > Binary – Dowloaded from postgres.org compiled and installed.
> > 
> > Hardware – Virtual Machine with 8vCPU and 32GB of RAM, on XFS filesystem.
> 
> I uploaded your query plan here: https://explain.depesz.com/s/14r6
> 
> The most expensive part is the merge join at the end.
> 
> Lines like this one: "Buffers: shared hit=676 read=306596, temp
> read=135840 written=135972"
> 
> Tell me that your sorts etc are spilling to disk, so the first thing
> to try is upping work_mem a bit. Don't go crazy, as it can run your
> machine out of memory if you do. but doubling or tripling it and
> seeing the effect on the query performance is a good place to start.
> 
> The good news is that most of your row estimates are about right, so
> the query planner is doing what it can to make the query fast, but I'm
> guessing if you get the work_mem high enough it will switch from a
> merge join to a hash_join or something more efficient for large
> numbers of rows.

Looking at the plan, I'd guess that the following index could be helpful:

CREATE INDEX ON ap.site_exposure(portfolio_id, peril_id, account_id);

Don't know how much it would buy you, but you could avoid the
sequential scan and the sort that way.

Yours,
Laurenz Albe

Unable to connect Postgres using psql while postgres is already running.

2018-01-10 Thread Dinesh Chandra 12108 
Hi Expert,

After restarting PostgreSQL Server, I am unable to connect postgres from putty, 
I am getting error

psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

While postgres is already running and also I am able to connect databases from 
PGAdmin tool but not from command prompt.

Software-postgresql-9.3
Os-Centos

Regards,
Dinesh Chandra
|Database administrator (Oracle/PostgreSQL)| Cyient Ltd. Noida.
--
Mobile: +91-9953975849 | Ext 1078 
|[email protected]
Plot No. 7, NSEZ, Phase-II ,Noida-Dadri Road, Noida - 201 305,India.





DISCLAIMER:

This email message is for the sole use of the intended recipient(s) and may 
contain confidential and privileged information. Any unauthorized review, use, 
disclosure or distribution is prohibited. If you are not the intended 
recipient, please contact the sender by reply email and destroy all copies of 
the original message. Check all attachments for viruses before opening them. 
All views or opinions presented in this e-mail are those of the author and may 
not reflect the opinion of Cyient or those of our affiliates.

Re: Unable to connect Postgres using psql while postgres is already running.

2018-01-10 Thread Laurenz Albe 
Dinesh Chandra 12108 wrote:
> After restarting PostgreSQL Server, I am unable to connect postgres from 
> putty, I am getting error
>  
> psql: could not connect to server: No such file or directory
> Is the server running locally and accepting
> connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
>  
> While postgres is already running and also I am able to connect databases 
> from PGAdmin tool but not from command prompt.

You know that a local connection only works when you are logged in
on the database machine, right?

Is your database listening on port 5432?

Connect as user "postgres" and run the following queries:

   SHOW port;
   SHOW unix_socket_directories;

That will show the port and the directories where UNIX sockets are created.

You can use a socket directory name with the -h option of psql.

Yours,
Laurenz Albe

Re: View preformance oracle to postgresql

2018-01-10 Thread Laurenz Albe 
Pavan Reddygari wrote:
> A view got converted to postgresql, performance while querying the view in 
> postgresql is 10X longer compared to oracle.
> Hardware resources are matching between oracle and postgresql.
>  
> V_item_attributes view code as below, same in oracle and postgresql.
> -
> SELECT a.iav_id,
> a.iav_itm_id,
> a.iav_iat_id,
> a.iav_value,
> a.iav_version,
> a.iav_approved,
> a.iav_create_date,
> a.iav_created_by,
> a.iav_modify_date,
> a.iav_modified_by,
> item_attribute.iat_id,
> item_attribute.iat_name,
> item_attribute.iat_type,
> item_attribute.iat_status,
> item_attribute.iat_requires_approval,
> item_attribute.iat_multi_valued,
> item_attribute.iat_inheritable,
> item_attribute.iat_create_date,
> item_attribute.iat_created_by,
> item_attribute.iat_modify_date,
> item_attribute.iat_modified_by,
> item_attribute.iat_translated
>FROM (item_attribute_value a
>  JOIN item_attribute ON ((a.iav_iat_id = item_attribute.iat_id)))
>   WHERE (a.iav_version = ( SELECT max(b.iav_version) AS max
>FROM item_attribute_value b
>   WHERE ((b.iav_itm_id = a.iav_itm_id) AND (b.iav_iat_id = 
> a.iav_iat_id;
>  
>  
> Oracle is using push predicate of IAV_ITM_ID column wherever 
> item_attribute_values table being used.
> Any alternatives available to reduce view execution time in postgresql 
> database or any hints, thoughts would be appreciated.

If (iav_version, iav_itm_id, iav_iat_id) is unique, you could use

   SELECT DISTINCT ON (a.iav_itm_id, a.iav_iat_id)
   ...
   FROM item_attribute_value a JOIN item_attribute b ON ...
   ORDER BY a.iav_version DESC;

Yours,
Laurenz Albe

RE: Re: Unable to connect Postgres using psql while postgres is already running.

2018-01-10 Thread Dinesh Chandra 12108 
Hi Laurenz Albe,

Thanks for your response.

But file   ".s.PGSQL.5432" does not exist .
How can I re-create this or any other option?

Regards,
Dinesh


-Original Message-
From: Laurenz Albe [mailto:[email protected]]
Sent: 10 January, 2018 2:41 PM
To: Dinesh Chandra 12108 ; 
[email protected]
Subject: [EXTERNAL]Re: Unable to connect Postgres using psql while postgres is 
already running.

Dinesh Chandra 12108 wrote:
> After restarting PostgreSQL Server, I am unable to connect postgres
> from putty, I am getting error
>
> psql: could not connect to server: No such file or directory
> Is the server running locally and accepting
> connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
>
> While postgres is already running and also I am able to connect databases 
> from PGAdmin tool but not from command prompt.

You know that a local connection only works when you are logged in on the 
database machine, right?

Is your database listening on port 5432?

Connect as user "postgres" and run the following queries:

   SHOW port;
   SHOW unix_socket_directories;

That will show the port and the directories where UNIX sockets are created.

You can use a socket directory name with the -h option of psql.

Yours,
Laurenz Albe



DISCLAIMER:

This email message is for the sole use of the intended recipient(s) and may 
contain confidential and privileged information. Any unauthorized review, use, 
disclosure or distribution is prohibited. If you are not the intended 
recipient, please contact the sender by reply email and destroy all copies of 
the original message. Check all attachments for viruses before opening them. 
All views or opinions presented in this e-mail are those of the author and may 
not reflect the opinion of Cyient or those of our affiliates.

Re: View preformance oracle to postgresql

2018-01-10 Thread Merlin Moncure 
On Tue, Jan 9, 2018 at 3:32 PM, Reddygari, Pavan  wrote:
>
> A view got converted to postgresql, performance while querying the view in 
> postgresql is 10X longer compared to oracle.
>
>FROM (item_attribute_value a
>  JOIN item_attribute ON ((a.iav_iat_id = item_attribute.iat_id)))
>   WHERE (a.iav_version = ( SELECT max(b.iav_version) AS max
>FROM item_attribute_value b
>   WHERE ((b.iav_itm_id = a.iav_itm_id) AND (b.iav_iat_id =
> a.iav_iat_id;

can you try rewriting the (more sanely formatted)
FROM item_attribute_value a
JOIN item_attribute ON a.iav_iat_id = item_attribute.iat_id
WHERE  a.iav_version =
  (
SELECT max(b.iav_version) AS max
FROM item_attribute_value b
WHERE
  b.iav_itm_id = a.iav_itm_id
  AND b.iav_iat_id = a.iav_iat_id
  );

to
FROM item_attribute_value a
JOIN item_attribute ON a.iav_iat_id = item_attribute.iat_id
JOIN
(
   SELECT max(b.iav_version) AS iav_version
   FROM item_attribute_value b
   GROUP BY iav_itm_id, iav_iat_id
) q USING (iav_itm_id, iav_iat_id, iav_version);

merlin

RE: PG 9.5 2 tables same DDL with diff size

2018-01-10 Thread Igor Neyman 
-Original Message-
From: ghiureai [mailto:[email protected]] 
Sent: Tuesday, January 09, 2018 5:54 PM
To: [email protected]
Subject: PG 9.5 2 tables same DDL with diff size

HI List

I am trying to understand the following :

have 2  identical PG cluster on diff hosts, same postgresql.conf, same db 
schema :

  same tale DDL and row counts but different size ( 14GB diff  ), I run reindex 
and full vacuum analyze,  but I can not decrease the size of larger table(50GB) 
to match the size in second

PG cluster.

any tips what can make this 2 tables to have diff size except the host ( same 
OS and PG version 9.5.3)?


Thank you


Table is still bloated because of some long running transactions, which don't 
allow full vacuum to do its job?

Regards,
Igor Neyman

RE: PG 9.5 2 tables same DDL with diff size

2018-01-10 Thread Isabella Ghiurea 
I run full vacuum and reindex on largest table (50GB) while there was no
server activities  so I assume no transaction was holding a lock on table
since the full vacuum was able to run, anything where I should consider
looking ?



--
Sent from: 
http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html

RE: PG 9.5 2 tables same DDL with diff size

2018-01-10 Thread Igor Neyman 

-Original Message-
From: Isabella Ghiurea [mailto:[email protected]] 
Sent: Wednesday, January 10, 2018 10:48 AM
To: [email protected]
Subject: RE: PG 9.5 2 tables same DDL with diff size

Attention: This email was sent from someone outside of Perceptron. Always 
exercise caution when opening attachments or clicking links from unknown 
senders or when receiving unexpected emails.


I run full vacuum and reindex on largest table (50GB) while there was no server 
activities  so I assume no transaction was holding a lock on table since the 
full vacuum was able to run, anything where I should consider looking ?


__

Yes, in  pg_stat_activity look for idle transactions that started long time ago.
To prevent vacuum from doing its job they don't need to lock the table, they 
could just prevent from cleaning "old" row versions.

Regards,
Igor Neyman

Re: PG 9.5 2 tables same DDL with diff size

2018-01-10 Thread ghiureai 



Thank you Igor, I was able to eliminate  the 15GB bloating for a 35GB 
table size  , only after I restart the      Pg server with one single 
connections and run a full vacuum for table.



Isabella
On 10/01/18 11:10 AM, Igor Neyman wrote:

-Original Message-
From: Isabella Ghiurea [mailto:[email protected]]
Sent: Wednesday, January 10, 2018 10:48 AM
To: [email protected]
Subject: RE: PG 9.5 2 tables same DDL with diff size

Attention: This email was sent from someone outside of Perceptron. Always 
exercise caution when opening attachments or clicking links from unknown 
senders or when receiving unexpected emails.


I run full vacuum and reindex on largest table (50GB) while there was no server 
activities  so I assume no transaction was holding a lock on table since the 
full vacuum was able to run, anything where I should consider looking ?


__

Yes, in  pg_stat_activity look for idle transactions that started long time ago.
To prevent vacuum from doing its job they don't need to lock the table, they could just 
prevent from cleaning "old" row versions.

Regards,
Igor Neyman

Query is slow when run for first time; subsequent execution is fast

2018-01-10 Thread Nandakumar M 
Hello,

This is my first question in postgres mailing list. If there are any
mistakes, please don't mind.

I am using PostgreSQL 9.4.4 on a Mac machine executing queries on postgres
server through the psql client.

servicedesk=# select version();

  version


 PostgreSQL 9.4.4 on x86_64-apple-darwin, compiled by
i686-apple-darwin11-llvm-gcc-4.2 (GCC) 4.2.1 (Based on Apple Inc. build
5658) (LLVM build 2336.11.00), 64-bit
(1 row)


Repeatedly, I came across instances where any query when run for the first
time takes longer time to execute (nearly 2 second sometimes), but
subsequent execution of the same query is very fast (less than 20
milliseconds).

The tables involved in the query also have very less number of rows (less
than 50).

On running explain (analyze, buffers) got the following results.


-- start --

servicedesk=#
servicedesk=# explain (analyze, buffers, verbose) SELECT COUNT(*) FROM
ChangeDetails LEFT JOIN SDOrganization AaaOrg ON
ChangeDetails.SITEID=AaaOrg.ORG_ID LEFT JOIN ApprovalStatusDefinition ON
ChangeDetails.APPR_STATUSID=ApprovalStatusDefinition.STATUSID LEFT JOIN
CategoryDefinition ON
ChangeDetails.CATEGORYID=CategoryDefinition.CATEGORYID LEFT JOIN
Change_Fields ON ChangeDetails.CHANGEID=Change_Fields.CHANGEID LEFT JOIN
Change_StageDefinition ON
ChangeDetails.WFSTAGEID=Change_StageDefinition.WFSTAGEID LEFT JOIN
Change_StatusDefinition ON
ChangeDetails.WFSTATUSID=Change_StatusDefinition.WFSTATUSID LEFT JOIN
AaaUser ChangeManager ON
ChangeDetails.CHANGEMANAGERID=ChangeManager.USER_ID LEFT JOIN AaaUser
ChangeOriginator ON ChangeDetails.INITIATORID=ChangeOriginator.USER_ID LEFT
JOIN AaaUser ChangeOwner ON ChangeDetails.TECHNICIANID=ChangeOwner.USER_ID
LEFT JOIN ChangeResolution ON
ChangeDetails.CHANGEID=ChangeResolution.CHANGEID LEFT JOIN ChangeTemplate
ON ChangeDetails.TEMPLATEID=ChangeTemplate.TEMPLATEID LEFT JOIN
ChangeToClosureCode ON ChangeDetails.CHANGEID=ChangeToClosureCode.CHANGEID
LEFT JOIN Change_ClosureCode ON
ChangeToClosureCode.ID=Change_ClosureCode.ID LEFT JOIN ChangeTypeDefinition
ON ChangeDetails.CHANGETYPEID=ChangeTypeDefinition.CHANGETYPEID LEFT JOIN
ChangeWF_Definition ON ChangeDetails.WFID=ChangeWF_Definition.ID LEFT JOIN
ImpactDefinition ON ChangeDetails.IMPACTID=ImpactDefinition.IMPACTID LEFT
JOIN ItemDefinition ON ChangeDetails.ITEMID=ItemDefinition.ITEMID LEFT JOIN
PriorityDefinition ON
ChangeDetails.PRIORITYID=PriorityDefinition.PRIORITYID LEFT JOIN
QueueDefinition ON ChangeDetails.GROUPID=QueueDefinition.QUEUEID LEFT JOIN
RiskDefinition ON ChangeDetails.RISKID=RiskDefinition.RISKID LEFT JOIN
StageDefinition ON ChangeDetails.STAGEID=StageDefinition.STAGEID LEFT JOIN
SubCategoryDefinition ON
ChangeDetails.SUBCATEGORYID=SubCategoryDefinition.SUBCATEGORYID LEFT JOIN
UrgencyDefinition ON ChangeDetails.URGENCYID=UrgencyDefinition.URGENCYID
LEFT JOIN SDUser ON ChangeDetails.INITIATORID=SDUser.USERID;





  QUERY PLAN






--
 Aggregate  (cost=13.25..13.26 rows=1 width=160) (actual time=0.018..0.018
rows=1 loops=1)
   Output: count(*)
   Buffers: shared hit=1
   ->  Seq Scan on public.changedetails  (cost=0.00..12.60 rows=260
width=160) (actual time=0.007..0.008 rows=2 loops=1)
 Output: changedetails.changeid, changedetails.initiatorid,
changedetails.technicianid, changedetails.stageid,
changedetails.priorityid, changedetails.categoryid,
changedetails.subcategoryid, changedetails.itemid,
changedetails.appr_statusid, changedetails.changetypeid,
changedetails.urgencyid, changedetails.title, changedetails.description,
changedetails.createdtime, changedetails.scheduledstarttime,
changedetails.scheduledendtime, changedetails.completedtime,
changedetails.notespresent, changedetails.siteid, changedetails.groupid,
changedetails.templateid, changedetails.wfid, changedetails.wfstageid,
changedetails.wfstatusid, changedetails.isemergency,
changedetails.isretrospective, changedetails.reason

Re: Query is slow when run for first time; subsequent execution is fast

2018-01-10 Thread Jeff Janes 
On Wed, Jan 10, 2018 at 3:59 AM, Nandakumar M  wrote:

>
> I am not using prepared statements. Postgres documentation and previous
> questions in the pgsql-performance mailing list mention that the query plan
> is cached only when prepared statements are used.
>
> https://www.postgresql.org/message-id/15600.1346885470%40sss.pgh.pa.us
>
> In the above thread Tom Lane mentions that the plan is never cached for
> raw queries. Yet, this is exactly what seems to be happening in my case. Am
> I missing something?
>

The query plan itself is not cached, but all the metadata about the (large
number) of tables used in the query is cached.  Apparently reading/parsing
that data is the slow step, not coming up with the actual plan.

> Please let me know how I can make sure the query execution for the first
time is fast too.

Don't keep closing and reopening connections.  Use a connection pooler
(pgbouncer, pgpool, whatever pooler is built into your
language/library/driver, etc.) if necessary to accomplish this.

Cheers,

Jeff