3
# updates the statistic of number of distinct attribute values
4
# (this should really be done by the vacuum command)
5
# this is kind of brute force and slow, but it works
6
# since we use SELECT DISTINCT to calculate the number of distinct values
7
# and that does a sort, you need to have plenty of disk space for the
8
# intermediate sort files.
14
# takes in a table and updates the attnvals columns for the attributes
17
# conn is the database connection
18
# rel is the table name
19
proc update_attnvals {conn rel} {
21
# first, get the oid of the rel
22
set res [pg_exec $conn "SELECT oid FROM pg_class where relname = '$rel'"]
23
if { [pg_result $res -numTuples] == "0"} {
24
puts stderr "update_attnvals: Relation named $rel was not found"
27
set oid [pg_result $res -getTuple 0]
30
# use this query to find the names of the attributes
31
set res [pg_exec $conn "SELECT * FROM $rel WHERE 'f'::bool"]
32
set attrNames [pg_result $res -attributes]
34
puts "attrNames = $attrNames"
35
foreach att $attrNames {
36
# find how many distinct values there are for this attribute
37
# this may fail if the user-defined type doesn't have
38
# comparison operators defined
39
set res2 [pg_exec $conn "SELECT DISTINCT $att FROM $rel"]
40
set NVALS($att) [pg_result $res2 -numTuples]
41
puts "NVALS($att) is $NVALS($att)"
42
pg_result $res2 -clear
46
# now, update the pg_attribute table
47
foreach att $attrNames {
48
# first find the oid of the row to change
49
set res [pg_exec $conn "SELECT oid FROM pg_attribute a WHERE a.attname = '$att' and a.attrelid = '$oid'"]
50
set attoid [pg_result $res -getTuple 0]
51
set res2 [pg_exec $conn "UPDATE pg_attribute SET attnvals = $NVALS($att) where pg_attribute.oid = '$attoid'::oid"]
56
# takes in a database name
57
# and updates the attnval stat for all the user-defined tables
59
proc updateStats { dbName } {
60
# datnames is the list to be result
61
set conn [pg_connect $dbName]
62
set res [pg_exec $conn "SELECT relname FROM pg_class WHERE relkind = 'r' and relname !~ '^pg_'"]
63
set ntups [pg_result $res -numTuples]
64
for {set i 0} {$i < $ntups} {incr i} {
65
set rel [pg_result $res -getTuple $i]
66
puts "updating attnvals stats on table $rel"
67
update_attnvals $conn $rel