Обсуждение: [GENERAL] The same query is too slow in some time of execution
Hi folks! I've a query with a join of two tables. One table have a 5 millions rows and child table have a 17 millions rows. The query is executed many times in application, every 20 seconds aproximately. The query normally execute in 2-3 seconds but in some time without apparent pattern the query is hang to 4-6 minutes is too slow to normally performance. I've configured 2 things: 1. Each table have indexes. First table have 11 index and second table have 7 2. I configured the VACUUM and ANALYZE run after 20,000 rows inserted. But apparantly the problem continues Best Regards! DRakoROd ----- Dame un poco de fe, eso me bastará. Rozvo Ware Solutions -- View this message in context: http://www.postgresql-archive.org/The-same-query-is-too-slow-in-some-time-of-execution-tp5951060.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 03/21/2017 04:27 PM, DrakoRod wrote: > Hi folks! > > I've a query with a join of two tables. One table have a 5 millions rows and > child table have a 17 millions rows. The query is executed many times in > application, every 20 seconds aproximately. > > The query normally execute in 2-3 seconds but in some time without apparent > pattern the query is hang to 4-6 minutes is too slow to normally > performance. > > I've configured 2 things: > > 1. Each table have indexes. First table have 11 index and second table have > 7 > > 2. I configured the VACUUM and ANALYZE run after 20,000 rows inserted. > > But apparantly the problem continues Well without seeing the query, the EXPLAIN ANALYZE of the query, the table schema(including indexes), the Postgres version, Postgres log info from the 'problem' times, system resource values and so on, there is really no way to answer this. For a longer version of the above see: https://wiki.postgresql.org/wiki/Guide_to_reporting_problems > > Best Regards! > DRakoROd > > > > ----- > Dame un poco de fe, eso me bastará. > Rozvo Ware Solutions > -- > View this message in context: http://www.postgresql-archive.org/The-same-query-is-too-slow-in-some-time-of-execution-tp5951060.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@aklaver.com
You're right, I'm sorry. At the moment, we review the schema for tables and indexes and decided redesigned. I detected a loop in the join, because we only have a integer sequencial like PK and no composite keys in the tables. I think that is the mainly problem. Thanks! ----- Dame un poco de fe, eso me bastará. Rozvo Ware Solutions -- View this message in context: http://www.postgresql-archive.org/The-same-query-is-too-slow-in-some-time-of-execution-tp5951060p5951841.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.