I'm developer a system, and the database was not made for me. In the beginning all query of the database are super fast, because i don't have any data. Along the project the database grown, but the performance decreased. When the client access a page of the site, the system will require a page on database. As a user of web-browser, everybody wants that page load as soon as possible. But today this query is take like 7,5 seconds:

enter image description here

So my page will load my article, at last with 7,3 seconds minimum (This query was made with no users accessing the database, only me). For a web-user, this is a eternity.

I really wants know, what exactly causes the PostgreSQL take this time to get my query. I make my research, and some people say the size of the database and the number of rows doesnt matter. I know what's is B* and Hash Table, but i really don't know how to understand why it's taking 7 seconds to make a simple select.

This table has: 5,5 millions of rows. And the database is heavy (For me). enter image description here

And this is the PC. But i think the problem is not here. (Or maybe yes?) enter image description here

What i want to understand what delay a query, like a simple select? Is the number of rows? The size of the tables?

enter image description here enter image description here

 Seq Scan on article_article  (cost=0.00..1151560.85 rows=1 width=14) 
 (actual time=6727.343..6733.021 rows=1 loops=1)
    Filter: ((title)::text = 'Brazil'::text)
    Rows Removed by Filter: 5655059
  Planning time: 0.134 ms
  Execution time: 6733.050 ms
  (5 rows)

enter image description here

  • 3
    Please run the EXPLAIN ANALYZE on your query. Next, copy the output into this site. Finally, share the link to this post. – Luan Huynh Jan 29 at 13:23
  • 3
    Also post the table and index definitions. – mustaccio Jan 29 at 13:33
  • 3
    @ViniciusMorais The statement ..the size of the database and the number of rows doesnt matter.. is not true. The difference can be (almost) imperceptible for the properly indexed tables - if indexes are suitable for queries. Otherwise the computational complexity grows linearly (best case) or even as power function (the case of joins on the number of tables). – Kondybas Jan 29 at 13:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.