Re: Wrong plan sequential scan instead of an index one - Mailing list pgsql-performance

-----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-----