1

Here I attached my query. It's getting 60141 ms to execute. I don't know what should i do. But I want to execute in short time.And now I posted my analyze and execute output of my query. Please help on this.

EXPLAIN (BUFFERS,ANALYZE) SELECT id 
FROM activitylog 
WHERE (url = '/staff/save/117' OR url = '/staff/create/117') 
AND timestamp > '1990-01-01 00:00:00' 
AND userid IN ( SELECT id 
                FROM users 
                WHERE companyid = ( SELECT companyid 
                                    FROM users 
                                    WHERE id='150' ) ) 
ORDER BY timestamp DESC

Output:

Sort  (cost=934879.83..934879.83 rows=1 width=12) (actual time=63918.947..63918.948 rows=4 loops=1)
    Sort Key: activitylog."timestamp"
    Sort Method: quicksort  Memory: 25kB
    Buffers: shared hit=168161 read=561433
    InitPlan 1 (returns $0)
    ->  Index Scan using "usersPrimary" on users users_1  (cost=0.14..8.16 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=1)
       Index Cond: (id = 150)
       Buffers: shared hit=2
    ->  Nested Loop  (cost=0.00..934871.66 rows=1 width=12) (actual time=63918.693..63918.917 rows=4 loops=1)
        Join Filter: (activitylog.userid = users.id)
        Rows Removed by Join Filter: 400
        Buffers: shared hit=168158 read=561433
    ->  Seq Scan on users  (cost=0.00..10.53 rows=25 width=4) (actual time=0.018..0.085 rows=101 loops=1)
        Filter: (companyid = $0)
        Rows Removed by Filter: 114
        Buffers: shared hit=10
    ->  Materialize  (cost=0.00..934860.39 rows=2 width=16) (actual time=120.024..632.858 rows=4 loops=101)
        Buffers: shared hit=168148 read=561433
    ->  Seq Scan on activitylog  (cost=0.00..934860.38 rows=2 width=16) (actual time=12122.376..63918.564 rows=4 loops=1)
        Filter: (("timestamp" > '2019-01-02 19:19:12.649837+00'::timestamp with time zone) AND (((url)::text = '/jobs/save/81924'::text) OR ((url)::text = '/jobs/create/81924'::text)))
        Rows Removed by Filter: 11935833
        Buffers: shared hit=168148 read=561433
    Planning time: 0.806 ms
    Execution time: 63919.748 ms

Thanks In advance.

Improve this question
0

3 Answers 3

Reset to default
4

Try this one, Join query will enhance the indexing and optimized query execution time

SELECT id
FROM activitylog
WHERE url in ('/staff/save/117','/staff/create/117')
  AND TIMESTAMP > '1990-01-01 00:00:00'
  AND EXISTS 
    (SELECT 1
     FROM users AS u
     JOIN users ur ON ur.CompanyID = u.CompanyID
     WHERE ur.ID = '150'
       AND u.id = activitylog.userid)
ORDER BY TIMESTAMP DESC
Improve this answer
Sign up to request clarification or add additional context in comments.

18 Comments

I tried it . But It shows error. ` column reference "id" is ambiguous`
Thanks for your quick reply. I tried the update one. But no improvement.
If your activitylog table having millions of records, try lazy loading by processing limited records first. example: page scroller reaches end of the page then show some bunch records.
It contains millions of records. But the result will be 10 or 15 rows. so we don't need page scroller.
Good work improving the query. Now all that is left to do is to replace the IN (SELECT ...) with an EXISTS (SELECT ...) and add the appropriate indexes.
|
2

You should rewrite the query using IN instead of OR like Pranesh Janarthanan's answer suggests, because OR is a performance killer.

In addition, you need indexes to avoid the expensive sequential scan on activitylog:

CREATE INDEX ON activitylog (timestamp);
CREATE INDEX ON activitylog (url);

Which of these indexes you need depends on how selective the respective conditions are.

Try with each of these indexes and with both (which may give you a “Bitmap And”) and keep what works best.

Improve this answer

18 Comments

We are using activitylog table for many purpose in many controllers. So I am afraid about create indexes. Is it affect other queries in controllers?
I wasn't aware that Postgres can't rewrite a series of OR conditions to an IN condition to make it more efficient. I will keep that in mind
@ViJ You are right to be afraid. activitylog sounds like something that will receive a lot of INSERTs, and that becomes substantially slower with each index. I told you how to make the query fast, you will have to make the balance between query speed and data modification speed.
@a_horse_with_no_name I have added a link to more of my ramblings on the subject.
That's a bummer. Even MySQL is able to use the same plan (single index access) in that case.
|
1

You should make sure you have the right indexes. Also, let's optimize the query:

SELECT id 
FROM activitylog 
WHERE url in ('/staff/save/117', '/staff/create/117') 
AND timestamp > '1990-01-01 00:00:00' 
AND 
(
(userid = 150) or
EXISTS
    (
        select 1
        from users workmate
        join users u150
        on workmate.companyid = u150.companyid and u150.id = 150
        and activitylog.userid = workmate.id
    )
)
ORDER BY timestamp DESC
Improve this answer

9 Comments

@Vi J try this updated query and post the execution time taken for this. We are waiting eagerly to know the results.
@Vi J replace it with your actual database column name userid. You must have this basic understanding.
@Pranesh Janarthanan I did it already in the morning and I ran. There is no improvement on it.It's getting late to retrieve the data
@PraneshJanarthanan actually there is still a possibility to improve the query (besides improving the indexes, of course). One could run the equivalent of a subquery to get the ids and then replace the exists part with userid in (nr1, nr2, ..., nrn). This should greatly improve performance, since the set of ids does not depend on the activitylog record.
@Vi J Show execution time of partial query: SELECT id FROM activitylog WHERE url in ('/staff/save/117', '/staff/create/117') AND timestamp > '1990-01-01 00:00:00' Also tell what (if any ) indexes did you set for mentioned tables ?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.