Results 1 to 2 of 2

Thread: Index problem

  1. #1
    Join Date
    Sep 2003
    Posts
    1

    Unanswered: Index problem

    Hi,

    I have a table containing columns:

    "END_DATE" timestamptz NOT NULL
    "REO_ID" int4 NOT NULL

    and i have indexed "REO_ID" coulumn.
    I have a query:

    select "REO_ID", "END_DATE" from "PRIORITY_STATISTICS" where "REO_ID" IN ('112851' ,'112859' ,'112871' ,'112883' ,'112891' ,'112904' ,'112915' ,'112924' ,'112939' ,'112947' ,'112960' ,'112984' ,'112999' ,'113013' ,'113032' ,'113059' ,'113067' ,'113084' ,'113096' ,'113103' ,'113110' ,'113117' ,'113125' ,'113132' ,'113139' ,'113146' ,'113153' ,'113160' ,'113167' ,'113174' ,'113181' ,'113188' ,'113195' ,'113204' ,'113268' ,'113279' ,'113294' ,'113302' ,'113317' ,'113340' ,'113358' ,'113385' ,'113404' ,'113412' ,'113419' ,'113429' ,'113436' ,'113443' ,'113571' ,'113636' ,'113649' ,'113689' ,'113705' ,'113744' ,'113755' ,'113724' ,'113737' ,'113812' ,'113828' ,'113762' ,'113842' ,'113869' ,'113925' ,'113976' ,'114035' ,'114044' ,'114057' ,'114070' ,'114084' ,'114094' ,'114119' )

    and it is _not_ using that index

    But following query (notice there are less id-s in WHERE clause, but rest is same)

    select "REO_ID", "END_DATE" from "PRIORITY_STATISTICS" where "REO_ID" IN ('112851' ,'112859' ,'112871' ,'112883' ,'112891' ,'112904' ,'112915' ,'112924' ,'112939' ,'112947' ,'112960' ,'112984' ,'112999' ,'113013' ,'113032' ,'113059' ,'113067' ,'113084' ,'113096' ,'113103' ,'113110' ,'113117' ,'113125' ,'113132' ,'113139' ,'113146' ,'113153' ,'113160' ,'113167' ,'113174' ,'113181' ,'113188' ,'113195' ,'113204' ,'113268' ,'113279' ,'113294' ,'113302' ,'113317' ,'113340' ,'113358' ,'113385' ,'113404' ,'113412' ,'113419' ,'113429' ,'113436' ,'113443' ,'113571' ,'113636' ,'113649' ,'113689' ,'113705' ,'113744' ,'113755' ,'113724' ,'113737' )

    _is_ using index:

    Index Scan using PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id on PRIORITY_STATISTICS (cost=0.00..394.06 rows=102 width=12)

    What causes this behaviour? is there any workaround? Suggestions?

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

    workaround

    Why don't you send to the server the command

    SET seq_scan=off;
    QUERY
    SET seq_scan=on;

    so the planner is forced to use indices
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

Posting Permissions

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