#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2002
    Location
    Malaysia
    Posts
    47
    Rep Power
    13

    Indexing In Postgres


    Hi Hi...
    I'm having some very interesting problem over here.
    I have a table that was indexed but there's not unique key in the table. When I tried to indexed it, it works but then when I set explain on the select statement, they won't make use of the index. Any idea what's happening ? or what is wrong with my indexing technique

    ex:
    Create table abc (
    name varchar(100),
    age integer
    )

    create index abc_name_idx on abc(name)

    when i tried this :

    explain select * from abc where name='john'

    they will use a seq scan instead of an index scan
  2. #2
  3. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    How many rows in the table? If the table is small, postgres will NOT use any indexes since it's not really needed.
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    1
    Rep Power
    0

    Post I have the same problem


    I have the same problem that rod k.
    And I have 14.520.000 rows.

    I dont know why postgres use Seq Scan when the index is there !

    If someone could give me a clue, I will appreciate.

    thanks,
    realmac.
  6. #4
  7. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    Post the query, the explain output and the schema of all tables involved. Without that, we would just be speculating.
  8. #5
  9. Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Location
    Romania
    Posts
    13
    Rep Power
    0
    I also have the same problem, with a "description" field of varchar(255).

    I use LIKE for searching and takes something like 10 seconds (300.000 rows). It is a little bit for a query.

    But I don't know what type of indexing should I use.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12
    what version of postgres are you guys using? also, you can use phppgadmin or if you're tricky and can do it with pg_dump you should see if the table structures reflect the index that you created. You could try to drop and then recreate the index as well. There are a few cases where postgres will not use an index, like rodk said if there are too few rows, but I think there is one other case??? something about hashes. I think you can build other types of indexes too, like R-tree index? I dunno but it's in the documentation.

    Here are some tidbits:

    While selecting 1000 out of 100000 rows could be a candidate for an index, selecting 1 out of 100 rows will hardly be, because the 100 rows will probably fit within a single disk page, and there is no plan that can beat sequentially fetching 1 disk page.

    so, from this it seems that it depends on the ratio of records you're removing with your select.

    When indexes are not used, it can be useful for testing to force their use. There are run-time parameters that can turn off various plan types (described in the Administrator's Guide). For instance, turning off sequential scans (enable_seqscan) and nested-loop joins (enable_nestloop), which are the most basic plans, will force the system to use a different plan. If the system still chooses a sequential scan or nested-loop join then there is probably a more fundamental problem for why the index is not used, for example, the query condition does not match the index. (What kind of query can use what kind of index is explained in the previous sections.)

    there's more




    here

IMN logo majestic logo threadwatch logo seochat tools logo