pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedproblem with from_collapse_limit and joined views
7+ messages / 3 participants
[nested] [flat]
* problem with from_collapse_limit and joined views
@ 2010-11-24 18:37 Markus Schulz <msc@antzsystem.de>
0 siblings, 1 reply; 7+ messages in thread
From: Markus Schulz @ 2010-11-24 18:37 UTC (permalink / raw)
To: pgsql-performance
hello,
i have a big performance problem with some views which would joined
(from the third party tool crystal reports) to print a document.
view1:
SELECT ...
FROM
personen.kunde kunde,
personen.natuerliche_person person,
viewakteur akteur,
personen.anschrift adresse,
personen.kontaktdaten kontakt,
konten.bankverbindung konto,
personen.berufsdaten beruf
WHERE person.objid = kunde.objid AND akteur.objid = kunde.objid AND
person.adresse = adresse.objid AND person.kontaktdaten = kontakt.objid
AND person.bankverbindung = konto.objid AND person.berufsdaten =
beruf.objid
view2:
SELECT ...
FROM vertraege.vertrag basisvertrag
JOIN ..
.. twelve more inner joins ..
Each view works alone very fast for objid-access.(no sequence scans)
The final query build by crystal reports was like:
SELECT ...
FROM view2 INNER JOIN view1 ON view2.kunde_objid = view1.objid
WHERE view2.objid = XXXX
as you can see the search-key for view1 comes from view2.
if i set "from_collapse_limit" (to merge the views) and
join_collapse_limit (to explode the explicit joins) high enough(approx
32), all is fine (good performance). But other queries are really slow
in our environment (therefore it's no option to raise the
join_collapse_limit to a higher value)
With defaults (8) for both, the performance is ugly because pgsql can't
explode the views to build a better join-table with view1.
(basisvertrag.kunde_objid from view2 is the key for kunde.objid from
view1).
As workaround nr.1 i can do the following:
SELECT ...
FROM view2 INNER JOIN view1 ON view2.kunde_objid = view1.objid
WHERE view2.objid = XXXX AND view1.objid = YYYY
yyyy (redundant information) is the same value as view2.kunde_objid.
This instructs pgsql to minimize the result of view1 (one entry).
But for this solution i must change hundreds of crystal report files.
For workaround nr.2 i need to instruct crystal report to generate a
cross-join:
SELECT ...
FROM view2 , view1
WHERE view2.VNID = view1.ID AND view2.ID = XXXX
Then i can slightly increase the from_collapse_limit (9) to enforce
pgsql to explode the view1 and build a better join-plan. But i don't
find a way to enforce crystal reports to using cross joins.
Workaround nr.3:
build one big view which contains all parts of view1 and view2.
Really ugly (view1 and view2 are used in many more places).
What are the other options?
Regards,
msc
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: problem with from_collapse_limit and joined views
@ 2010-12-03 20:32 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Markus Schulz <msc@antzsystem.de>
0 siblings, 1 reply; 7+ messages in thread
From: Kevin Grittner @ 2010-12-03 20:32 UTC (permalink / raw)
To: Markus Schulz <msc@antzsystem.de>; pgsql-performance
Markus Schulz <msc@antzsystem.de> wrote:
> i have a big performance problem
> [joining two complex views for reporting]
What version of PostgreSQL is this? How is PostgreSQL configured?
(The postgresql.conf file with all comments removed is good.)
-Kevin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: problem with from_collapse_limit and joined views
@ 2010-12-04 10:24 Markus Schulz <msc@antzsystem.de>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 7+ messages in thread
From: Markus Schulz @ 2010-12-04 10:24 UTC (permalink / raw)
To: pgsql-performance; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Am Freitag, 3. Dezember 2010 schrieb Kevin Grittner:
> Markus Schulz <msc@antzsystem.de> wrote:
> > i have a big performance problem
> >
> > [joining two complex views for reporting]
>
> What version of PostgreSQL is this? How is PostgreSQL configured?
> (The postgresql.conf file with all comments removed is good.)
Production System is 8.4 (config attached).
But i've tried 9.0 with same result.
Regards
msc
Attachments:
postgresql.conf (text/plain, 1.8K) download
data_directory = '/var/lib/postgresql/8.4/main' # use data in another directory
hba_file = '/etc/postgresql/8.4/main/pg_hba.conf' # host-based authentication file
ident_file = '/etc/postgresql/8.4/main/pg_ident.conf' # ident configuration file
external_pid_file = '/var/run/postgresql/8.4-main.pid' # write an extra PID file
listen_addresses = '*' # what IP address(es) to listen on;
port = 5432 # (change requires restart)
max_connections = 3100 # (change requires restart)
unix_socket_directory = '/var/run/postgresql' # (change requires restart)
ssl = true # (change requires restart)
shared_buffers = 1500MB # min 128kB
max_prepared_transactions = 20 # zero disables the feature
work_mem = 16MB # min 64kB
checkpoint_segments = 8 # in logfile segments, min 1, 16MB each
random_page_cost = 3.0 # same scale as above
effective_cache_size = 20GB
from_collapse_limit = 8
join_collapse_limit = 8 # 1 disables collapsing of explicit
log_destination = 'stderr' # Valid values are combinations of
logging_collector = on # Enable capturing of stderr and csvlog
log_directory = '/var/log/pgsql' # directory where log files are written,
log_filename = 'postgresql-%Y-%m-%d_%H.log' # log file name pattern,
log_rotation_age = 120 # Automatic rotation of logfiles will
log_duration = on
log_line_prefix = '%t [%d] [%x][%p]: [%l-1] ' # special values:
log_statement = 'none' # none, ddl, mod, all
track_functions = all # none, pl, all
track_activity_query_size = 16384
log_statement_stats = off
autovacuum_max_workers = 10 # max number of autovacuum subprocesses
datestyle = 'iso, dmy'
lc_messages = 'C' # locale for system error message
lc_monetary = 'de_DE.utf8' # locale for monetary formatting
lc_numeric = 'de_DE.utf8' # locale for number formatting
lc_time = 'de_DE.utf8' # locale for time formatting
default_text_search_config = 'pg_catalog.german'
* Re: problem with from_collapse_limit and joined views
@ 2010-12-04 15:29 Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 2 replies; 7+ messages in thread
From: Kevin Grittner @ 2010-12-04 15:29 UTC (permalink / raw)
To: msc@antzsystem.de; pgsql-performance
Markus Schulz 11/24/10 1:02 PM >>>
> if i set "from_collapse_limit" (to merge the views) and
> join_collapse_limit (to explode the explicit joins) high enough
> (approx 32), all is fine (good performance). But other queries are
> really slow in our environment (therefore it's no option to raise
> the join_collapse_limit to a higher value)
>
> With defaults (8) for both, the performance is ugly
One option would be to create a different user for running queries
which read from complex views such as this.
postgres=# create user bob;
CREATE ROLE
postgres=# alter user bob set from_collapse_limit = 40;
ALTER ROLE
postgres=# alter user bob set join_collapse_limit = 40;
ALTER ROLE
Log in as bob, and your queries should run fine.
Nothing leapt out at me as an issue in your postgresql.conf except:
max_prepared_transactions = 20
Do you actually use prepared transactions? (Sometimes people confuse
this with prepared statements, which are a completely different
feature.)
-Kevin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: problem with from_collapse_limit and joined views
@ 2010-12-04 16:59 Tom Lane <tgl@sss.pgh.pa.us>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 2010-12-04 16:59 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: msc@antzsystem.de; pgsql-performance
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Markus Schulz 11/24/10 1:02 PM >>>
>> if i set "from_collapse_limit" (to merge the views) and
>> join_collapse_limit (to explode the explicit joins) high enough
>> (approx 32), all is fine (good performance). But other queries are
>> really slow in our environment (therefore it's no option to raise
>> the join_collapse_limit to a higher value)
>>
>> With defaults (8) for both, the performance is ugly
> One option would be to create a different user for running queries
> which read from complex views such as this.
If you don't want to change the collapse limits, the only other option
is to restructure this specific query so that its syntactic structure
is closer to the ideal join order. Look at the plan you get in the
good-performing case and re-order the join syntax to look like that.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: problem with from_collapse_limit and joined views
@ 2010-12-04 18:20 Markus Schulz <msc@antzsystem.de>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 0 replies; 7+ messages in thread
From: Markus Schulz @ 2010-12-04 18:20 UTC (permalink / raw)
To: pgsql-performance; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Am Samstag 04 Dezember 2010 schrieb Kevin Grittner:
> One option would be to create a different user for running queries
> which read from complex views such as this.
>
> postgres=# create user bob;
> CREATE ROLE
> postgres=# alter user bob set from_collapse_limit = 40;
> ALTER ROLE
> postgres=# alter user bob set join_collapse_limit = 40;
> ALTER ROLE
>
> Log in as bob, and your queries should run fine.
thanks, that was really an option for us to use a different user for
creating the reports.
> Nothing leapt out at me as an issue in your postgresql.conf except:
>
> max_prepared_transactions = 20
>
> Do you actually use prepared transactions? (Sometimes people confuse
> this with prepared statements, which are a completely different
> feature.)
yes, they are needed for our JPA-based j2ee application.
regards
msc
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: problem with from_collapse_limit and joined views
@ 2010-12-04 18:46 Markus Schulz <msc@antzsystem.de>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Markus Schulz @ 2010-12-04 18:46 UTC (permalink / raw)
To: pgsql-performance; +Cc: Tom Lane <tgl@sss.pgh.pa.us>
Am Samstag 04 Dezember 2010 schrieb Tom Lane:
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
...
> > One option would be to create a different user for running queries
> > which read from complex views such as this.
>
> If you don't want to change the collapse limits, the only other
> option is to restructure this specific query so that its syntactic
> structure is closer to the ideal join order. Look at the plan you
> get in the good-performing case and re-order the join syntax to look
> like that.
no that's not working in this case.
view1 and view2 are written with explicit joins and no better join was
possible. Each view works perfect standalone.
In my above example i have rewritten view1 without explicit joins only
for testing purpose. Without explicit joins i can gather the optimal
query plan from a slightly higher from_collapse_limit (see workaround 2
from my initial posting).
If both views using explicit joins the from_collapse_limit is useless
(only join_collapse_limit usable).
The problem exists only for "view1 JOIN view2" and that pgsql don't
"see" that an element of view2 contains an index-access for reducing the
data from view1. Only if he can break the complete join of both views
into one query-plan he can "see" this. But for this i must raise the
limits.
Looks like some improvement to the geco optimizer was needed here ;)
regards
msc
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2010-12-04 18:46 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-11-24 18:37 problem with from_collapse_limit and joined views Markus Schulz <msc@antzsystem.de>
2010-12-03 20:32 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-12-04 10:24 ` Markus Schulz <msc@antzsystem.de>
2010-12-04 15:29 Re: problem with from_collapse_limit and joined views Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-12-04 16:59 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-12-04 18:46 ` Markus Schulz <msc@antzsystem.de>
2010-12-04 18:20 ` Markus Schulz <msc@antzsystem.de>
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