pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: Gaetano Mendola <mendola@bigfoot.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Cc: Richard Huxton <dev@archonet.com>
Subject: Re: Wrong plan sequential scan instead of an index one
Date: Fri, 30 Mar 2007 12:15:53 +0200
Message-ID: <460CE359.8060208@bigfoot.com> (raw )
In-Reply-To: <460CDCDC.7020901@archonet.com >
References: <460CD941.6080303@bigfoot.com >
<460CDCDC.7020901@archonet.com >
-----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-----
view thread (15+ messages) latest in thread
Message-ID: <460CE359.8060208@bigfoot.com>
Permalink: ../460CE359.8060208@bigfoot.com/
Also on: postgresql.org/message-id/460CE359.8060208@bigfoot.com
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mendola@bigfoot.com, dev@archonet.com
Subject: Re: Wrong plan sequential scan instead of an index one
In-Reply-To: <460CE359.8060208@bigfoot.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox