Hi,
On 06/03/2009 06:42 PM, Tom Lane wrote:
Andres Freund<and...@anarazel.de> writes:
On 06/03/2009 06:21 PM, Tom Lane wrote:
I find this *really* hard to believe, because I've never seen the bitmap
support operations show up noticeably at all in profiles. What sort of
queries are you testing?
Many left joins from one base relation to additional dimensions. All the
dimensions are relatively complex views consisting out of multiple joins
or subselects.
Some correlated subqueries and some [NOT] EXISTS() are also included in
some of the queries.
Hmmm, could you provide a complete test case? I'm thinking the behavior
might indicate some other performance issue, ie an unreasonable number
of bitmapset calls in some particular planning path.
Ok. I tried to reproduce it using only pg_catalog and suceeded to some
degree:
- The query is pointless, pointless, pointless
- The effect is only around 5-10% instead of the 15-20% I have measured
(fewer tables involved - fewer cache misses?)
- The query is crazy, but so is the one on the schema in question
- I could get more consistent results with geqo disabled, but the
results are in the same ballpark whether enabled or not
- Sometimes adding a single join more/less dropped the planning time to
a fraction - strange.
- The same with changing {join,from}_collapse_limit - sometimes changing
it yields plan times different by orders of magnitudes in both directions.
On the real data its naturally not only one view but multiple ones...
And there are fewer views involved, but they are more complex (including
EXISTS(), and some subqueries).
Plan time (averaged) without change:
cnt: 40 (4 times per session)
avg: 4572ms
Plan time (averaged) with change:
cnt: 40 (4 times per session)
avg: 4236ms
~7% difference. Same with higher number of repetitions and with most
other planner settings I tried
Now thats not a lot of change, but again, this is smaller than with the
original queries.
Does that help?
Andres
SET geqo = on;
SET geqo_effort = 5;
SET geqo_threshold = 12;
SET join_collapse_limit = 8;
SET from_collapse_limit = 8;
--/*
DROP VIEW IF EXISTS public.pg_class_with_attributes;
CREATE VIEW public.pg_class_with_attributes AS
SELECT pg_class.oid, pg_class.relnamespace, pg_class.reltype,
pg_attribute.atttypid, other.oid AS otheroid
FROM pg_class
JOIN pg_attribute ON
pg_class.oid = pg_attribute.attrelid
JOIN pg_type ON
pg_attribute.atttypid = pg_type.oid
JOIN pg_constraint ON
pg_constraint.conrelid = pg_class.oid
JOIN pg_class other ON
other.oid = pg_constraint.confrelid
JOIN pg_locks ON
pg_locks.relation = pg_class.oid
OR
pg_locks.relation = other.oid
JOIN pg_stat_activity ON
pg_locks.pid = pg_stat_activity.procpid
JOIN pg_tablespace ON
pg_class.reltablespace = pg_tablespace.oid
OR
other.reltablespace = pg_tablespace.oid
LEFT JOIN pg_stat_all_tables ON
pg_stat_all_tables.relid = pg_class.oid
OR
pg_stat_all_tables.relid = other.oid
LEFT JOIN pg_stat_all_indexes ON
pg_stat_all_indexes.relid = pg_class.oid
OR
pg_stat_all_indexes.relid = other.oid
JOIN pg_attrdef ON
pg_attribute.attrelid = pg_attrdef.adrelid
AND
pg_attribute.attnum = pg_attrdef.adnum;
;
--*/
PREPARE bench_1 AS
SELECT *
FROM pg_class
JOIN pg_class_with_attributes AS pg_class_with_attributes_1 ON
pg_class.oid = pg_class_with_attributes_1.oid
JOIN pg_class_with_attributes AS pg_class_with_attributes_2 ON
pg_class.oid = pg_class_with_attributes_2.oid
JOIN pg_class_with_attributes AS pg_class_with_attributes_3 ON
pg_class.oid = pg_class_with_attributes_3.oid
JOIN pg_class_with_attributes AS pg_class_with_attributes_4 ON
pg_class.oid = pg_class_with_attributes_4.oid
JOIN pg_class_with_attributes AS pg_class_with_attributes_5 ON
pg_class.oid = pg_class_with_attributes_5.oid
JOIN pg_class_with_attributes AS pg_class_with_attributes_6 ON
pg_class.oid = pg_class_with_attributes_6.oid
JOIN pg_class_with_attributes AS pg_class_with_attributes_7 ON
pg_class.oid = pg_class_with_attributes_7.oid
JOIN pg_class_with_attributes AS pg_class_with_attributes_8 ON
pg_class.oid = pg_class_with_attributes_8.oid
JOIN pg_class_with_attributes AS pg_class_with_attributes_9 ON
pg_class.oid = pg_class_with_attributes_9.oid
JOIN pg_class_with_attributes AS pg_class_with_attributes_10 ON
pg_class.oid = pg_class_with_attributes_10.oid
JOIN pg_class_with_attributes AS pg_class_with_attributes_11 ON
pg_class.oid = pg_class_with_attributes_11.oid
JOIN pg_class_with_attributes AS pg_class_with_attributes_12 ON
pg_class.oid = pg_class_with_attributes_12.oid
JOIN pg_class_with_attributes AS pg_class_with_attributes_13 ON
pg_class.oid = pg_class_with_attributes_13.oid
JOIN pg_class_with_attributes AS pg_class_with_attributes_14 ON
pg_class.oid = pg_class_with_attributes_14.oid
JOIN pg_class_with_attributes AS pg_class_with_attributes_15 ON
pg_class.oid = pg_class_with_attributes_15.oid
JOIN pg_class_with_attributes AS pg_class_with_attributes_16 ON
pg_class.oid = pg_class_with_attributes_16.oid
JOIN pg_class_with_attributes AS pg_class_with_attributes_17 ON
pg_class.oid = pg_class_with_attributes_17.oid
JOIN pg_class_with_attributes AS pg_class_with_attributes_18 ON
pg_class.oid = pg_class_with_attributes_18.oid
JOIN pg_class_with_attributes AS pg_class_with_attributes_19 ON
pg_class.oid = pg_class_with_attributes_19.oid
;
DEALLOCATE bench_1;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
