From: | "Gene Selkov, Jr(dot)" <selkovjr(at)mcs(dot)anl(dot)gov> |
---|---|
To: | pgsql-general(at)postgreSQL(dot)org |
Subject: | Yet another btree gotcha |
Date: | 1999-10-06 05:33:20 |
Message-ID: | 199910060633.BAA28519@antares.mcs.anl.gov |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Nikos Mouat | 1999-10-06 05:38:11 | You are really hosed. |
Previous Message | Bruce Momjian | 1999-10-06 04:52:26 | Re: [GENERAL] Confusion in Insert Query syntax |