Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2009
    Posts
    2

    Unanswered: Where clause filtering a view is not using index

    Hi everybody,
    i have this scenario. I have a view that groups data from tables A,B,C, grouping is by primary key column of table A.id (big integer). Table A contains around 500 000 records.
    Now when want to use the view in a query like this:

    select
    *
    from
    myview
    where
    myview.id in (select id from A where somecriteria)

    i get full table scans instead of index seeks by A.id, even if somecriteria returns very few rows from table A. This is all strange because if i do something like this, i DO get my index seek:

    select
    *
    from
    myview
    where
    myview.id in (select id from A where id = 30000)

    but when i do this, i get table scan again:

    select
    *
    from
    myview
    where
    myview.id in (select id from A where id in (30000,30001))
    -- if we instead type 'myview.id in (30000,30001)', we get index seek again

    Now i see it has something to do do evaluation of left and right side expression of operator IN, but i feel there is a bug we see here, as 2nd and 3rd case should be equivalent.

    So my question is how can i use a view with grouping inside it, utilizing the index column it is grouped by.

    Thanks in advance

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by boudinov
    So my question is how can i use a view with grouping inside it, utilizing the index column it is grouped by.
    Post the DDL of the tables involved including indexes
    Post the DDL of the view

  3. #3
    Join Date
    Mar 2009
    Posts
    2
    It is a rather big view definition, but here it is in brief:
    Table A columns: id(bigserial,primary key),start_time(timestamp),end_time(timestamp),dir ection(bit)
    Table B columns: id,fk_A_id(foreign key to table A),b1,b2
    Table C columns: id,fk_A_id,c1,c2

    MyView is something like this:

    select
    A.id,
    max(A.start_time),
    max(A.end_time),
    sum(B.b1),
    avg(B.b2),
    sum(C.c1),
    avg(C.c2)
    from
    A
    inner join B on b.fk_A_id = A.id
    left outer join C on C.fk_A_id = A.id
    where
    A.direction = 1
    group by A.id

    The query that is doing the undesired table scans:

    select
    MyView.*
    from
    MyView
    where
    MyView.id in (select id from A where start_time between :start_time and :end_time)

    A,B,C tables may have several hunred of records and it takes several tens of seconds to join all of them and then filter on the 'in' operator. Even if the subquery returns only like 4 records.

    but if i do like this:

    select
    MyView.*
    from
    MyView
    where
    MyView.id in (5000,5005,5012,5023)

    it uses index seek (or bitmap seek/scan, not quite sure) on A.id and query is processed for several milliseconds.

    So the question is common, about filtering a view, in which you have grouping. And you want that filter to utilize an index column, by which you did the grouping.

    Maybe my concept is wrong in the beginning, and maybe i should instead use a user defined function passing :start_time,:end_time and do the filtering inside. But if the function returns too many records i may have troubles with memory buffers, passing a big function result set to the caller. Especially if there are many nested functions.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    You should learn to use [code] tags around your SQL code. That makes reading the SQL a lot more readable.

    You could try to replace your sub-query with a JOIN condition:
    Code:
    select
    MyView.*
    from MyView 
      JOIN A on myView.id = a.id and a.start_time between :start_time and :end_time
    Don't know if that will change anything though

    Or if you always limit the result of the view with that condition, I'd join the table A in the view already

Posting Permissions

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