On 13.3.2015 21:46, Vivekanand Joshi wrote:
> Since I was doing it only for the testing purposes and on a
> development server which has only 8 GB of RAM, I used only 10m rows.
> But the original table has 1.5 billion rows. We will obviously be
> using a server with very high capacity, but I am not satisfied with
> the performance at all. This might be only a start, so I might get a
> better performance later.
OK, understood.
> Yes, the view is complex and almost is created by using 10 tables. Same
> goes with other views as well but this is what we are using in Netezza
> as well. And we are getting results of the full report in less than 5
> seconds. And add to that, this is only a very little part of the whole
> query used in a report.
Well, in the very first message you asked "Is the query written
correctly as per the PostgreSQL?" - how can we decide that when most of
the query is hidden in some unknown view?
> I will post the result of whole query with Explain analyze tomorrow.
Please also collect some information about the system using iostat,
vmstat and such, so that we know what is the bottleneck.
> We might even consider taking experts advice on how to tune queries
> and server, but if postgres is going to behave like this, I am not
> sure we would be able to continue with it.
That's probably a good idea.
--
Tomas Vondra http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services