Re: Wrong plan sequential scan instead of an index one

I don't know about postgres, but in oracle it could be better to write:

SELECT COUNT(distinct c.id)
FROM t_oa_2_00_card c,l_pvcp l
WHERE l.value ilike '%pi%' and c.pvcp=l.id;

or

SELECT COUNT(c.id)
FROM t_oa_2_00_card c,
(select distinct id from l_pvcp where value ilike '%pi%') l
WHERE c.pvcp=l.id;

depending how many rows, what kind of rows, ... are in l_pvcp table.

having index in t_oa_2_00_card.pvcp can slow queries in oracle.

Ismo

On Fri, 30 Mar 2007, Gaetano Mendola wrote:

> -----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-----
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>