Bikram Kesari Naik wrote > Hi David, > > We have indexes on all the columns which are used in the where clause and > these tables are linked by foreign key constraint. > > > Thanks, > Bikram > > -----Original Message----- > From: > pgsql-performance-owner@ > [mailto: > pgsql-performance-owner@ > ] On Behalf Of David Johnston > Sent: Friday, March 07, 2014 11:53 AM > To: > pgsql-performance@ > Subject: Re: Slow query > > Bikram Kesari Naik wrote >> Hi, >> >> I have a view which joins multiple tables to give me a result. It >> takes more than a minute to give me the result on psql prompt when I >> select all the data from that view. >> The single CPU which is used to run this query is utilized 100%.Even >> if I fire a count(*) it takes 10 Sec. I wanted to know if there is >> anything we can do to speedup this query below 1 sec. > > In all likelihood you need to index your foreign keys, and possibly other > fields, but as you haven't provided table and index definitions it is hard > to say for sure. > > Idepeing on how many rows are hidden I'm not sure an unqualified query on > this view can run in 1/60th the time even with indexes present - the > sequential scans are efficient if the proportion of the tables being > returned is high. > > David J. Read these. https://wiki.postgresql.org/wiki/Using_EXPLAIN https://wiki.postgresql.org/wiki/SlowQueryQuestions And note, while the FK thing is likely not relevant in this situation defining a constraint does not cause an index to be created. Depending on your usage patterns defining those indexes can be helpful. One last thought: not only are your row counts high but it seems like your row sizes may also be large due to them containing binary content. You likely need to take a different approach to solving whatever unspecified problem this query is intended to solve if you need sub-second performance. That all said the main area of improvement for this is system memory concerns so, as noted in the links above, play with that and see what happens. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Slow-query-tp5795077p5795086.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
