-1

When i am taking count(*) from my base query it is taking more than 30 min to get the result. instead of count(*), added '*' the query executed so fast. Here is the explain details.

base query :

explain
select -- QUERY 1
    sec.ts,t.cnt
from 
postgres.seconds sec 
inner  JOIN lateral   
(
select count(*) as cnt
from postgres.query_execution query_exe  
where sec.ts between query_exe.start_time and query_exe.end_time 
) t on true where t.cnt > 0

explain plan details:

Nested Loop  (cost=349.78..474530186.42 rows=1356481 width=16)                        
  ->  Seq Scan on seconds sec  (cost=0.00..19567.81 rows=1356481 width=8)             
  ->  Aggregate  (cost=349.78..349.79 rows=1 width=8)                                 
        Filter: (count(*) > 0)                                                        
        ->  Seq Scan on query_execution query_exe  (cost=0.00..347.00 rows=1111 width=
              Filter: ((sec.ts >= start_time) AND (sec.ts <= end_time))

when i added count(*) ,

explain
select count(*) from ( -- added -- Query 2
select 
    sec.ts,t.cnt
from 
schoolofit.seconds sec 
inner  JOIN lateral   
(
select count(*) as cnt
from schoolofit.query_execution query_exe  
where sec.ts between query_exe.start_time and query_exe.end_time 
) t on true where t.cnt > 0) s;
          
----------------------------------------------------------------------------------------------+
Aggregate  (cost=474533577.62..474533577.63 rows=1 width=8)                                   |
  ->  Nested Loop  (cost=349.78..474530186.42 rows=1356481 width=0)                           |
        ->  Seq Scan on seconds sec  (cost=0.00..19567.81 rows=1356481 width=8)               |
        ->  Aggregate  (cost=349.78..349.79 rows=1 width=8)                                   |
              Filter: (count(*) > 0)                                                          |
              ->  Seq Scan on query_execution query_exe  (cost=0.00..347.00 rows=1111 width=0)|
                    Filter: ((sec.ts >= start_time) AND (sec.ts <= end_time))        

     |

I could not find the reason why count(*) is taking more time. Is there any better approach to find the count. Please advise.

FYI .The base Query result count is 390269

Learn Hadoop
  • 2,760
  • 8
  • 28
  • 60
  • 1
    The slow query returns 1.3 million rows while the fast one returns 1 row. That's a siginificant difference. – The Impaler Aug 16 '23 at 02:38
  • QUERY -1 returning all the records executed with in few seconds. But Query 2 is returning count of all rows and single row function which is taking more time – Learn Hadoop Aug 16 '23 at 02:55
  • You don't she any times. Do `explain (analyze, buffers)` – jjanes Aug 16 '23 at 04:16
  • i think explain (analyze, buffers) command for physical tables. the above one joining two tables and getting result.. – Learn Hadoop Aug 16 '23 at 04:50
  • Is there a typo or is the empty argument here intentional count() vs count(*), because this "instead of count(), added '*' the query executed so fast" is confusing. Is the problem just that select takes much longer than select count(*)? Maybe there's a version of postgres that supports the bare count(), in which case I'd figure it's doing a window function rather than a simple aggregate. But at least in Postgres 15 that syntax isn't supported. – Error_2646 Aug 16 '23 at 13:22
  • Corrected the typo.. – Learn Hadoop Aug 16 '23 at 17:44
  • *I think explain (analyze, buffers) command for physical tables.* This is wrong. Use `explain (analyze, buffers)` with any select or dml regardless of number of tables, views, sub-queries, joins, function calls, etc. – Belayer Aug 17 '23 at 01:59
  • can you please provide some example. i will try – Learn Hadoop Aug 17 '23 at 02:50

0 Answers0