Results 1 to 5 of 5

Thread: Indexing issue

  1. #1
    Join Date
    Sep 2003
    Posts
    3

    Unanswered: Indexing issue

    I'm working on a rather large, complicated view, and I've noticed that one of the subqueries I do several times in the view uses a sequential scan and is extremely slow. The weird thing is that there already is an index on the column that is being joined, and that column is joined to other columns in the view and the index is used! The subquery uses IN to link the tables. Here is an example of the query that is being slow

    SELECT * FROM response WHERE key IN (SELECT related_keys FROM merged_keys_view WHERE lookup_key = 50000);

    I found that if I take out the subselect, and just insert a list of keys in there, it still uses a sequential scan. However, if I explicitly cast each key in the list to an int8, then the index scan works. Has anyone seen this behavior before? All the relevant keys are defined as int8's in the schema, but it seems like when I use the IN clause, it causes the processor to forget that these are int8's and therefore not use the index. Is there any way to force it, or to cast all the entries returned from my subselect into int8's? Even if I turn of sequential scans, this still performs a sequential scan until I explicitly cast each value.

    Thanks

    -Rob

  2. #2
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    index faq

    see index faq

    single quotes
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  3. #3
    Join Date
    Sep 2003
    Posts
    3
    Thanks for the suggestion but that did not help either. After looking over your index faq, i also realized I probably should have mentioned that I've tried setting enable_seqscan = off before doing this and the sequential scan still happens unless I explicitly cast the results (which I can't do because I want them to come from the subselect). Any other suggestions?

  4. #4
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    IN

    did you make single quotes around it ='50000'
    also do this with int8

    If this does not work

    Try the new 7.4
    There is a planner improvement with subselects
    I think 7.4beta3 is now out

    If you need it in a production system forget this suggestion.

    Cast the results.CAst to what?
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  5. #5
    Join Date
    Sep 2003
    Posts
    3
    Again, thanks for the continued help. Yes, I did try putting quotes around the '50000'. Also, I tried '50000'::bigint. What I mean by "casting the results" is this:

    My example sql statement given before was:

    SELECT * FROM response WHERE key IN (SELECT related_keys FROM merged_keys_view WHERE lookup_key = 50000);

    The problem where sequential scan rather than index scan is occurring at the IN clause. What I have found is that if I remove the SELECT statement and instead do something like the following, an indexed scan works:

    SELECT * FROM response WHERE key IN (50000::bigint, 50123::bigint, 55442::bigint);

    If I just list the keys, and don't explicitly cast each one to an int8 (bigint) then it uses a sequential scan again. As I've mentioned before, this occurs regardless of the enable_seqscan setting. I have also tried something as follows, which if the subselect returns a single result, works great using the index scan, but if the subselect returns multiple keys, then I get an error stating something like I'm trying to cast a set into a single element. If you need the exact error, I'll run that query again and try it, but here is the query:

    SELECT * FROM response WHERE key IN ((SELECT related_keys FROM merged_keys_view WHERE lookup_key = 50000)::bigint);

    As far as your 7.4beta suggestion, I'm trying to get my sysadmin here to install it at least to play with, but this is a production environment and I won't get 7.4 for real until it's released final and been tested for a while.

Posting Permissions

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