pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedViews and query planner
4+ messages / 3 participants
[nested] [flat]
* Views and query planner
@ 2006-06-30 12:35 Mario Splivalo <mario.splivalo@mobart.hr>
0 siblings, 1 reply; 4+ messages in thread
From: Mario Splivalo @ 2006-06-30 12:35 UTC (permalink / raw)
To: pgsql-sql
Let's say I have a view like this:
CREATE VIEW v_small AS
SELECT c1, c2
FROM t1 JOIN t2 ON t1.c1 = t2.c2
and then I have another view
CREATE VIEW v_big AS
SELECT c1 FROM v_small WHERE c2 > 5
Now, when I do this:
SELECT * FROM v_big WHERE c1 < 1500
Is postgres going to make one query plan, or each view has it own query
plan?
Mike
--
Mario Splivalo
Mob-Art
mario.splivalo@mobart.hr
"I can do it quick, I can do it cheap, I can do it well. Pick any two."
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Views and query planner
@ 2006-06-30 12:47 Michael Glaesemann <grzm@seespotcode.net>
parent: Mario Splivalo <mario.splivalo@mobart.hr>
0 siblings, 1 reply; 4+ messages in thread
From: Michael Glaesemann @ 2006-06-30 12:47 UTC (permalink / raw)
To: mario.splivalo@mobart.hr; +Cc: pgsql-sql
On Jun 30, 2006, at 21:35 , Mario Splivalo wrote:
> Is postgres going to make one query plan, or each view has it own
> query
> plan?
Have you taken a look at the EXPLAIN ANALYZE output of the queries?
You can see exactly which plan PostgreSQL will use.
Michael Glaesemann
grzm seespotcode net
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Views and query planner
@ 2006-06-30 13:23 Mario Splivalo <mario.splivalo@mobart.hr>
parent: Michael Glaesemann <grzm@seespotcode.net>
0 siblings, 1 reply; 4+ messages in thread
From: Mario Splivalo @ 2006-06-30 13:23 UTC (permalink / raw)
To: Michael Glaesemann <grzm@seespotcode.net>; +Cc: pgsql-sql
On Fri, 2006-06-30 at 21:47 +0900, Michael Glaesemann wrote:
> On Jun 30, 2006, at 21:35 , Mario Splivalo wrote:
>
> > Is postgres going to make one query plan, or each view has it own
> > query
> > plan?
>
> Have you taken a look at the EXPLAIN ANALYZE output of the queries?
> You can see exactly which plan PostgreSQL will use.
>
So, the performance will be the same if I use view on view, or I create
big select with those view definitions.
Mario
--
Mario Splivalo
Mob-Art
mario.splivalo@mobart.hr
"I can do it quick, I can do it cheap, I can do it well. Pick any two."
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Views and query planner
@ 2006-06-30 14:13 Markus Schaber <schabi@logix-tt.com>
parent: Mario Splivalo <mario.splivalo@mobart.hr>
0 siblings, 0 replies; 4+ messages in thread
From: Markus Schaber @ 2006-06-30 14:13 UTC (permalink / raw)
To: pgsql-sql
Hi, Mario,
Mario Splivalo wrote:
>>> Is postgres going to make one query plan, or each view has it own
>>> query
>>> plan?
>> Have you taken a look at the EXPLAIN ANALYZE output of the queries?
>> You can see exactly which plan PostgreSQL will use.
>
> So, the performance will be the same if I use view on view, or I create
> big select with those view definitions.
Yes, usually[1]. Internally, PostgreSQL generates a big query out of the
stack of views, and then lets the optimizer do its work.
The query performance will be the same, but the planning performance may
vary. (This effect can be mildered by careful use of prepared statements.)
HTH,
Markus
[1] Queries reaching some complexity level may lead to different plans,
due to optimizer limits, the gequo kicking in, or other effects.
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-06-30 14:13 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-06-30 12:35 Views and query planner Mario Splivalo <mario.splivalo@mobart.hr>
2006-06-30 12:47 ` Michael Glaesemann <grzm@seespotcode.net>
2006-06-30 13:23 ` Mario Splivalo <mario.splivalo@mobart.hr>
2006-06-30 14:13 ` Markus Schaber <schabi@logix-tt.com>
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