hi,

i have a btree index on col1 in table1. The column has either values
1,2,3, or 4. 4 does not appear that much in the table (only 5 times).
there are about 20 million rows in the table. when i do a "select *
from table1 where col1=4" it takes very long time to get back to me
(around 4 minutes). why is it taking so long if i have an index on
it? I also tried this with a hash index and it was still slow.

thanx, amir

Search Discussions

  • Gaetano Mendola at Aug 17, 2004 at 11:42 pm

    Amir Zicherman wrote:
    hi, >
    i have a btree index on col1 in table1. The column has either values
    1,2,3, or 4. 4 does not appear that much in the table (only 5 times).
    there are about 20 million rows in the table. when i do a "select *
    from table1 where col1=4" it takes very long time to get back to me
    (around 4 minutes). why is it taking so long if i have an index on
    it? I also tried this with a hash index and it was still slow.
    May I see the result of
    explain analyze select * from table1 where col1 = 4;

    and also the table definition could be usefull.

    Did you run recently an analyze on your table ?


    Regards
    Gaetano Mendola
  • Bruno Wolff III at Aug 18, 2004 at 6:09 pm

    On Tue, Aug 17, 2004 at 14:06:11 -0700, Amir Zicherman wrote:
    hi,

    i have a btree index on col1 in table1. The column has either values
    1,2,3, or 4. 4 does not appear that much in the table (only 5 times).
    there are about 20 million rows in the table. when i do a "select *
    from table1 where col1=4" it takes very long time to get back to me
    (around 4 minutes). why is it taking so long if i have an index on
    it? I also tried this with a hash index and it was still slow.
    With that kind of distribution, you may be better off with a partial
    index on the table for col1=4 instead of the current index. If the
    other values show up with roughly equal frequency, you aren't going to
    want an index scan to be used anyway, so you might as well use the
    smaller partial index.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedAug 17, '04 at 9:06p
activeAug 18, '04 at 6:09p
posts3
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase