cancel
Showing results for 
Search instead for 
Did you mean: 

[Webinar] Programming the SQL Way with Common Table Expressions

Community Manager

[Webinar] Programming the SQL Way with Common Table Expressions

email_logo.png

The Pulse of Postgres Slim.png

 

Coming Soon: Programming the SQL Way with Common Table Expressions

With Bruce Momjian

 

Date: November 6th, 2019
Time: 10.00am ET | 16.00 CET | 15.00 GMT

 

Join Postgres expert, Bruce Momjian, as he discusses common table expressions (CTEs) and the ability to enable queries to be more imperative, allowing looping and processing hierarchical structures that are normally associated only with imperative languages.

Highlights include:
  • The comparison between imperative and declarative programming languages
  • Examples of syntax & recursive CTEs
  • Writeable CTEs and the importance of using CTEs
 
About the Speaker: 
Image_Bruce_Circle.png
Bruce Momjian, co-founder of

 

the PostgreSQL Global Development Group and Database Architect at EnterpriseDB
 
 
 
1 REPLY 1
Highlighted
Community Manager

Re: [Webinar] Programming the SQL Way with Common Table Expressions

Follow Up: [WEBINAR]  Programming the SQL Way with Common Table Expressions

 

We hope you had a chance to join us for the webinar!!   If not, don't worry! 

You can view the webinar via the link below. Be sure to check out the Q & A Session too!

 

 

Q & A SESSION:

__________________

 

Question:

If performance improvements are in v12, do you recommend caution or avoiding using in v10 and v11?

 

Answer:

Well, for CTEs that use non-SELECT queries or recursion, those can’t be inlined so there is no change in PG 12.  For the other queries, just be aware of the optimization fence, but many people use CTEs in those releases with minimal or no performance impact.

_____

 

Question:

Is there better performance for using the CTE rather than subquery or is it just preference:

 

DELETE FROM retdemo2

WHERE retdemo2.x < (SELECT AVG(x) FROM retdemo2);

 

vs

 

WITH source (average) AS (

SELECT AVG(x) FROM retdemo2

)

DELETE FROM retdemo2 USING source

WHERE retdemo2.x < source.average;

 

Answer:

It is really just a preference for the case you showed.

_____

 

Question:

Can you please post the link to the sample code?

 

Answer:

Sure! Here it is: http://momjian.us/main/writings/pgsql/cte.sql

_____

Question:

Would this perform better than doing a standard join?

 

WITH class AS (

SELECT oid, relname

FROM pg_class

WHERE relkind = 'r'

)

SELECT class.relname, attname

FROM pg_attribute, class

WHERE class.oid = attrelid

ORDER BY 1, 2

LIMIT 5;

 

Answer:

That is slower in pre-PG 12 and the same in PG 12 and later.

_____

Question:

Is the recursive syntax similar to the connect by syntax used in Oracle?

 

Answer:

Yes, it performs a similar function.

_____

Question:

So, as a practical example, if I am moving old data to an archive table, I can handle the delete and insert in the same statement for the data?

 

Answer:

Yes, that is a big win.

_____

 

Question:

Will CTE provide performance enhancement replacing simple subqueries or only in imperative queries?

 

Answer:

CTEs usually are only faster when the combine multiple queries into a single one.

_____