Thread: Views and query planner

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."

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

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."

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