Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2012
    Posts
    2

    Question Unanswered: How to speed up this query? Using index?

    Is it possible to speed up the query bellow? It operates on table t1 with more than 500 000 rows and takes around 45 seconds. Without condition "AND t3.date = date_trunc('day', t1.date)" it executes only for 200 miliseconds.

    Code:
    SELECT t1.id, t1.start_date, t1.date, t2.name, t3.count
    	FROM t1, t2, t3
    	WHERE
    		start_date IS NOT NULL
    		AND t1.id = t2.id
    		AND t3.date = date_trunc('day', t1.date)
    	ORDER BY t1.date NULLS LAST
    	LIMIT 10
    I use following indicies:

    Code:
    CREATE INDEX CONCURRENTLY i1
    	ON t1 (date_trunc('day', date));
    
    CREATE INDEX CONCURRENTLY i2
    	ON t1 (date NULLS LAST)
    	WHERE start_date IS NOT NULL
    
    CREATE INDEX CONCURRENTLY i3
    	ON t1 (date_trunc('day', date))
    	WHERE start_date IS NOT NULL
    
    CREATE INDEX CONCURRENTLY i4
    	ON t1 (date_trunc('day', date), date NULLS LAST)
    	WHERE start_date IS NOT NULL

  2. #2
    Join Date
    Mar 2012
    Posts
    10
    Indexing is not the issue here. You have no join condition between t3 and any other tables. Ie. Its a cartesian........

    FROM t1, t2, t3
    WHERE
    start_date IS NOT NULL
    AND t1.id = t2.id
    AND t3.date = date_trunc('day', t1.date)


    You should hve a t1.somefield = t3.somefield or perhaps t2.somefield = t3.somefield etc.

    After that is resolved if performance still is an issue indexing may help.

  3. #3
    Join Date
    Mar 2012
    Posts
    2
    Thank you for your reply!

    I have t3.date = date_trunc('day', t1.date) Isn't this enough?

    Never mind. I changed the structure of the database.

  4. #4
    Join Date
    Mar 2012
    Posts
    10
    You are right. I must have been low on coffee when I read it.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about reversing column order in i4?
    (I don't know wheather it is possible and effective on PostgreSQL.)
    Code:
    CREATE INDEX CONCURRENTLY i5
    	ON t1 (date NULLS LAST, date_trunc('day', date))
    	WHERE start_date IS NOT NULL
    Last edited by tonkuma; 04-14-12 at 14:27. Reason: Add "and effective".

  6. #6
    Join Date
    Jun 2010
    Posts
    17
    How about Increasing sorting memory parameter i.e work_mem in session level.

    postgres=# set work_mem='50MB'

    and then execute SELECT statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •