Re: Wrong plan sequential scan instead of an index one

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFGDONZ7UpzwH2SGd4RAhs3AKCYWgyn3vkzDvhWl/tF1TRs/nDT7QCeJDZu
k9hQ0WBS1cFHcCjIs3jca0Y=
=RIDE
-----END PGP SIGNATURE-----