Richard Huxton writes:
> Ah, but it's got no way of knowing what matches you'll get for
> '%anything%'. There's no easy way to get statistics for matching substrings.
8.2 actually tries the match on the most-common-values list, if said
list is big enough (I think the threshold is stats target =
Gaetano Mendola wrote:
Michael Fuhr wrote:
On Fri, Mar 30, 2007 at 04:46:11AM -0600, Michael Fuhr wrote:
Have you tried increasing the statistics target on l_pvcp.value?
I ran your queries against canned data in 8.2.3 and better statistics
resulted in more accurate row count estimates for this
Gaetano Mendola wrote:
The match 19 for '%pi%' is estimated, the real matches are:
test=# select id from l_pvcp where value ilike '%pi%';
id
-
62
(1 row)
test=# select id from l_pvcp where value ilike 'pi';
id
-
62
(1 row)
so one row in both cases, that's why I expect for both
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Michael Fuhr wrote:
> On Fri, Mar 30, 2007 at 04:46:11AM -0600, Michael Fuhr wrote:
>> Have you tried increasing the statistics target on l_pvcp.value?
>> I ran your queries against canned data in 8.2.3 and better statistics
>> resulted in more accurat
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Michael Fuhr wrote:
> On Fri, Mar 30, 2007 at 12:08:26PM +0200, Gaetano Mendola wrote:
>> Claus Guttesen wrote:
>>> Try changing random_page_cost from the default 4 to 2 in postgresql.conf:
>>>
>>> random_page_cost = 2
>> I have tuned that number alrea
On Fri, Mar 30, 2007 at 04:46:11AM -0600, Michael Fuhr wrote:
> Have you tried increasing the statistics target on l_pvcp.value?
> I ran your queries against canned data in 8.2.3 and better statistics
> resulted in more accurate row count estimates for this and other
> parts of the plan. I don't r
On Fri, Mar 30, 2007 at 12:08:26PM +0200, Gaetano Mendola wrote:
> Claus Guttesen wrote:
> > Try changing random_page_cost from the default 4 to 2 in postgresql.conf:
> >
> > random_page_cost = 2
>
> I have tuned that number already at 2.5, lowering it to 2 doesn't change
> the plan.
The followi
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
Gaetano Mendola wrote:
Richard Huxton wrote:
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
-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
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Claus Guttesen wrote:
>> Hi all,
>> take a look at those plans:
>
> Try changing random_page_cost from the default 4 to 2 in postgresql.conf:
>
> random_page_cost = 2
>
> The default in postgresql is somewhat conservative. This setting
> indicates f
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) (
Hi all,
take a look at those plans:
Try changing random_page_cost from the default 4 to 2 in postgresql.conf:
random_page_cost = 2
The default in postgresql is somewhat conservative. This setting
indicates for postgresql how fast your disks are, the lower the
faster.
Could this setting be cha
-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%');
QUERY PLAN
-
-
14 matches
Mail list logo
