Thread

Yet another btree gotcha

selkovjr@mcs.anl.gov — 1999-10-06T05:33:20Z

I am wondering whether it's normal to see dropping a btree improve the
query which could never complete enough that it completes in a blink?

I realize the data I have here represent the worst possible input to
btree, and I'm probably better off without any index at all, but I
guess it is something that the optimizer or the access method itself
should be able to decide.

I am joining two tables, "km" and "su" on an int2 attribute, "item".
Just take a look at the value histograms for item:

emp=> SELECT item, count (*) AS count FROM km GROUP BY item;
item|count
----+-----
   1|31262
   2|  110
   3|    3
   4|    1
(4 rows)

emp=> SELECT item, count (*) AS count FROM su GROUP BY item;
item|count
----+-----
   1|94108
   2| 1697
   3|  773
   4|  482
   5|  237
   6|  146
   7|  105
   8|   68
   9|   41
  10|   29
  11|   22
  12|   15
  13|   13
  14|    8
  15|    7
  16|    6
  17|    5
  18|    4
  19|    4
  20|    4
  21|    4
  22|    3
  23|    3
  24|    3
  25|    1
  26|    1
  27|    1
  28|    1
  29|    1
  30|    1
  31|    1

As a default rule, I used to create the btree indices for all integer
types, regardless of their values. Not anymore. It took me quite a
while to figure that the following query did not work because of the
faulty btree index on "item" (other joined attributes are char()):

SELECT km.km, su.su 
  FROM km, su 
  WHERE km.id = su.id 
    AND km.rel = su.rel
    AND km.item = su.item 
    AND su ~ '^ethanol';

Can the btree or any other AM be smart enough and bail out from CREATE
INDEX saying, "your data isn't worth indexing"?

--Gene