Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Location
    Europe->Sweden->Stockholm
    Posts
    71

    Unanswered: Not using index where it should?

    Hi,
    I am having problems with a query that is taking a lot of time.

    What I want to do is to set the value of a column to "TRUE" if a (column1, column2) pair exists in another table.

    I figured that the simple
    Code:
    UPDATE tab1 SET col3=TRUE WHERE (col1, col2) IN (SELECT col1, col2 FROM tab2)
    would quickly update all rows in tab1 where (col1, col2) is in tab2.

    An EXPLAIN says:
    Code:
                                   QUERY PLAN                               
    ------------------------------------------------------------------------
     Seq Scan on tab1  (cost=0.00..713075.00 rows=500 width=17)
       Filter: (subplan)
       SubPlan
         ->  Seq Scan on tab2  (cost=0.00..1426.10 rows=90510 width=8)
    I thought that PostgreSQL would, for every row in the sub query, try to find the (col1, col2) pair in tab1 since there is an index on those two columns. Doing it as above seems backwards to me, and takes a long time (24 hours+). How can I rewrite the query, so that PostgreSQL does not do a scan on both tables (115,563 x 90,510 = 10,459,607,130 rows to scan)?

    Thanks in advance!
    Last edited by snorp; 09-25-04 at 07:10. Reason: "rows" should be "columns" in last paragraph

  2. #2
    Join Date
    Sep 2004
    Posts
    46
    I'm not sure but you may try this:

    UPDATE tab1 SET col3=TRUE WHERE EXISTS( SELECT tab1.col1,tab1.col2 FROM tab2 WHERE tab1.col1=tab2.col2 AND tab1.col2=tab2.col2)
    Last edited by cristiu; 09-25-04 at 14:18. Reason: forget some details

  3. #3
    Join Date
    Apr 2004
    Location
    Europe->Sweden->Stockholm
    Posts
    71
    Well, what can I say? The query now runs 115,000 times faster than it did before. Thank you!

  4. #4
    Join Date
    Sep 2004
    Posts
    46
    Welcome! I think you see a little mistake in my suggestion :
    WHERE tab1.col1=tab2.col2 must be : tab1.col1=tab2.col1

Posting Permissions

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