1

I am trying to optimize this query :

SELECT sum(a-alias.s_count) AS count 
FROM a-table a-alias 
WHERE
(
    a-alias.created_at_minute_id >= 
    (floor(extract(epoch from(
        select select_to_time 
        from a_run_table 
        where timeunit = 'hour'
        and status = 'done'
        order by select_to_time desc limit 1)) / 60))
AND(a-alias.s_id = 'some-uuid-number'));

This gives me the sum of s_count and the result is a number like 29087. This query takes 60 milliseconds.

I found that if I replace:

(floor(extract(epoch from(
    select select_to_time 
    from a_run_table 
    where timeunit = 'hour'
    and status = 'done'
    order by select_to_time desc limit 1)) / 60))

...with some constant number like say 246589, the query time reduces to 1.2 milliseconds.

I moved this part to the With clause, but I am not able to make it work:

WITH last_run AS 
(
    SELECT (floor(extract(epoch from(
        select select_to_time 
        from a_run_table 
        where timeunit = 'hour'
        and status = 'done'
        order by select_to_time desc limit 1)) / 60)) as 'time'
)
SELECT sum(a-alias.s_count) AS count 
FROM a-table a-alias 
WHERE (a-alias.created_at_minute_id >= (select time from last_run)      
AND(a-alias.s_id = 'some-uuid-number'));

I am not able to get value from last_run using (select time from last_run).

I expect a number from the sum function. From floor query, I want to get a number which would be used for >= in Where clause.

Other information

Running query gives :

WITH last_run AS (SELECT (floor(extract(epoch from(select select_to_time
from a_run_table where timeunit = 'hour' and status = 'done' 
order by select_to_time desc limit 1)) / 60)) as time) select time from last_run;

Time:

+-------------+
|        time |
|-------------|
| 2.42925e+07 |
+-------------+

Also:

pgcli --version
Version: 0.20.1

Structure of a_run_table :

| id               | uuid                        |  not null   |
| status           | character varying(50)       |  not null   |
| select_to_time   | timestamp without time zone |  not null   |
| timeunit         | character varying(20)

How should I write my query so that I can take value dynamically from outer-query and send it to inner query?

  • A question about performance cannot exist without basic information like Postgres version, table definition and a simple description of what your query should achieve. Please consider instructions in the postgresql-performance tag I added. – Erwin Brandstetter Mar 17 '16 at 2:42
  • @ErwinBrandstetter I am not asking about performance but how to rewrite the query with with clause. It is a syntax quetion. – Ashish Negi Mar 22 '16 at 2:40
  • 2
    @AshishNegi: Seems to me like there is a lot about performance. Be that as it may, we need the basic information I mentioned either way. And we shouldn't have to ask for it. It should be in your question to begin with. – Erwin Brandstetter Mar 22 '16 at 2:55
  • sorry for late reply.. i have updated with new details. – Ashish Negi Mar 22 '16 at 11:14
  • 1
    Please add the EXPLAIN (ANALYZE, BUFFERS) output of the slow query. Without it it is really like asking people to check their crystal balls. Mine is broken, for one. – dezso Mar 22 '16 at 11:26

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.