Richard Huxton wrote:
Gaetano Mendola wrote:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1

Hi all, take a look at those plans:


test=# explain analyze SELECT COUNT(id) FROM t_oa_2_00_card WHERE
pvcp in (select id from l_pvcp where value ilike '%pi%');
-> Hash IN Join (cost=2.22..153835.49 rows=177404 width=8) (actual
time=2.908..4001.814 rows=7801 loops=1) Hash Cond: ("outer".pvcp =
"inner".id)
Isn't too much choose a sequential scan due to 19 estimated rows when
with 4 estimated does a correct index scan ?
I don't think it's the matches on l_pvcp that's the problem, it's the
fact that it thinks its getting 177404 rows matching the IN.

Now, why 19 rows from the subquery should produce such a large estimate
in the outer query I'm not sure. Any strange distribution of values on
pvcp?
I don't know what do you mean for strange, this is the distribution:

test=# select count(*) from t_oa_2_00_card;
count
- --------
877682
(1 row)

test=# select count(*), pvcp from t_oa_2_00_card group by pvcp;
count | pvcp
- -------+------
13 |
2 | 94
57 | 93
250 | 90
8158 | 89
4535 | 88
3170 | 87
13711 | 86
5442 | 85
2058 | 84
44 | 83
1 | 82
4 | 80
1 | 79
14851 | 78
12149 | 77
149 | 76
9 | 75
4 | 74
2 | 73
5 | 72
28856 | 71
12847 | 70
8183 | 69
11246 | 68
9232 | 67
14433 | 66
13970 | 65
3616 | 64
2996 | 63
7801 | 62
3329 | 61
949 | 60
35168 | 59
18752 | 58
1719 | 57
1031 | 56
1585 | 55
2125 | 54
9007 | 53
22060 | 52
2800 | 51
5629 | 50
16970 | 49
8254 | 48
11448 | 47
20253 | 46
3637 | 45
13876 | 44
19002 | 43
17940 | 42
5022 | 41
24478 | 40
2374 | 39
4885 | 38
3779 | 37
3532 | 36
11783 | 35
15843 | 34
14546 | 33
29171 | 32
5048 | 31
13411 | 30
6746 | 29
375 | 28
9244 | 27
10577 | 26
36096 | 25
3827 | 24
29497 | 23
20362 | 22
8068 | 21
2936 | 20
661 | 19
8224 | 18
3016 | 17
7731 | 16
8792 | 15
4486 | 14
3 | 13
6859 | 12
4576 | 11
13377 | 10
14578 | 9
6991 | 8
52714 | 7
6477 | 6
11445 | 5
24690 | 4
10522 | 3
2917 | 2
34694 | 1
(92 rows)


I think that estimate is something like: 877682 / 92 * 19


Regards
Gaetano Mendola

Search Discussions

Discussion Posts

Previous

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 5 of 15 | next ›
Discussion Overview
grouppgsql-performance @
categoriespostgresql
postedMar 30, '07 at 9:33a
activeApr 2, '07 at 10:42a
posts15
users7
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase