I am running 6.5.3 on a tables with >300,000 rows. I've seen lots of
cases where queries
fail if I haven't done a vacuum analyze; I understand this is one of the
quirks of PostgreSQl.
However, below is an example of a query that fails if I HAVE done a
vacuum analyze. Therefore,
I have no way of getting this database into a state where all queries
will perform as expected!
Any ideass,
atest=> SELECT COUNT(s_1.s_doc) FROM s_n_grantor AS s_1,s_n_grantee AS
s_2 WHERE
s_1.value LIKE 'davis j%' AND s_2.value LIKE 'east%' AND
s_1.s_doc=s_2.s_doc
atest-> ;
count
-----
30
(1 row)
atest=> explain SELECT COUNT(s_1.s_doc) FROM s_n_grantor AS
s_1,s_n_grantee AS s
_2 WHERE s_1.value LIKE 'davis j%' AND s_2.value LIKE 'east%' AND
s_1.s_doc=s_2.
s_doc;
NOTICE: QUERY PLAN:
Aggregate (cost=10.56 rows=1 width=24)
-> Nested Loop (cost=10.56 rows=1 width=24)
-> Index Scan using s_n_grantee_pkey on s_n_grantee s_2
(cost=8.56 row
s=1 width=12)
-> Index Scan using s_n_grantor_pkey on s_n_grantor s_1
(cost=2.01 row
s=1 width=12)
EXPLAIN
atest=> vacuum analyze
atest-> ;
VACUUM
atest=>
atest=> SELECT COUNT(s_1.s_doc) FROM s_n_grantor AS s_1,s_n_grantee AS
s_2 WHERE
s_1.value LIKE 'davis j%' AND s_2.value LIKE 'east%' AND
s_1.s_doc=s_2.s_doc;
count
-----
0
(1 row)
atest=> explain SELECT COUNT(s_1.s_doc) FROM s_n_grantor AS
s_1,s_n_grantee AS s
_2 WHERE s_1.value LIKE 'davis j%' AND s_2.value LIKE 'east%' AND
s_1.s_doc=s_2.
s_doc;
NOTICE: QUERY PLAN:
Aggregate (cost=4836.60 rows=1 width=24)
-> Merge Join (cost=4836.60 rows=1 width=24)
-> Index Scan using s_n_grantee_pkey on s_n_grantee s_2
(cost=2182.43
rows=18 width=12)
-> Index Scan using s_n_grantor_pkey on s_n_grantor s_1
(cost=2651.53
rows=62 width=12)
