Re: Wrong plan sequential scan instead of an index one

Поиск
Список
Период
Сортировка
От ismo.tuononen@solenovo.fi
Тема Re: Wrong plan sequential scan instead of an index one
Дата
Msg-id Pine.LNX.4.64.0703301335580.3355@ismoli.solenovo.jns
обсуждение исходный текст
Ответ на Re: Wrong plan sequential scan instead of an index one  (Gaetano Mendola <mendola@bigfoot.com>)
Список pgsql-performance
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
>

В списке pgsql-performance по дате отправления:

Предыдущее
От: Richard Huxton
Дата:
Сообщение: Re: Wrong plan sequential scan instead of an index one
Следующее
От: Michael Fuhr
Дата:
Сообщение: Re: Wrong plan sequential scan instead of an index one