-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
-
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
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)
-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 for
-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
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
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
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 following
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
-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 already at 2.5,
-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 accurate row
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
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
Richard Huxton dev@archonet.com 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
14 matches
Mail list logo
