Re: Performance issues

On 14.3.2015 00:28, Vivekanand Joshi wrote:
> Hi Guys,
>
> So here is the full information attached as well as in the link
> provided below:
>
> http://pgsql.privatepaste.com/41207bea45
>
> I can provide new information as well.

Thanks.

We still don't have EXPLAIN ANALYZE - how long was the query running (I
assume it got killed at some point)? It's really difficult to give you
any advices because we don't know where the problem is.

If EXPLAIN ANALYZE really takes too long (say, it does not complete
after an hour / over night), you'll have to break the query into parts
and first tweak those independently.

For example in the first message you mentioned that select from the
S_V_D_CAMPAIGN_HIERARCHY view takes ~9 minutes, so start with that. Give
us EXPLAIN ANALYZE for that query.

Few more comments:

(1) You're using CTEs - be aware that CTEs are not just aliases, but
impact planning / optimization, and in some cases may prevent
proper optimization. Try replacing them with plain views.

(2) Varadharajan Mukundan already recommended you to create index on
s_f_promotion_history.send_dt. Have you tried that? You may also
try creating an index on all the columns needed by the query, so
that "Index Only Scan" is possible.

(3) There are probably additional indexes that might be useful here.
What I'd try is adding indexes on all columns that are either a
foreign key or used in a WHERE condition. This might be an
overkill in some cases, but let's see.

(4) I suspect many of the relations referenced in the views are not
actually needed in the query, i.e. the join is performed but
then it's just discarded because those columns are not used.
Try to simplify the views as much has possible - remove all the
tables that are not really necessary to run the query. If two
queries need different tables, maybe defining two views is
a better approach.

(5) The vmstat / iostat data are pretty useless - what you provided are
averages since the machine was started, but we need a few samples
collected when the query is running. I.e. start the query, and then
give us a few samples from these commands:

iostat -x -k 1
vmstat 1

> Would like to see if queries of these type can actually run in
> postgres server?

Why not? We're running DWH applications on tens/hundreds of GBs.

> If yes, what would be the minimum requirements for hardware? We would
> like to migrate our whole solution on PostgreSQL as we can spend on
> hardware as much as we can but working on a proprietary appliance is
> becoming very difficult for us.

That's difficult to say, because we really don't know where the problem
is and how much the queries can be optimized.

--
Tomas Vondra http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services