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: [PERFORM] 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.