February 4th, 2015 by depesz | Tags: , , , | 5 comments »

I was working today on some updates to client database. While doing it, I figured it would be simpler if I saw all "codenames" and ids of rows from dictionary table - not so big. But it was bigger than ...

A colleague recently let me know that anonymization in explain.depesz.com doesn't handle CTE names. For example, in plan: ...

So, couple of days ago, some guy, from Periscope company wrote a blogpost about getting number of distinct elements, per group, faster using subqueries. This was then submitted to Hacker News and r/Programming on Reddit. Then, the original authors submitted second blogpost ...

In this, hopefully 2nd to last, post in the series, I will cover the rest of usually happening operations that you can see in your explain outputs.

On 1st of February, Peter Eisentraut committed patch: Add CREATE RECURSIVE VIEW syntax This is specified in the SQL standard. The CREATE RECURSIVE VIEW specification is transformed into a normal CREATE VIEW statement with a WITH RECURSIVE clause. reviewed by Abhijit Menon-Sen and Stephen ...

December 9th, 2012 by depesz | Tags: , , , , , , , | 2 comments »

In PostgreSQL 8.4 we got CTE - Common Table Expressions. Since then we have this great tool available, but apparently for some people it's still black magic. CuTE, but still magic. I'll try to make it a bit less magical, ...

Yesterday on irc someone asked: Hi, how do I get top 5 values from a column group by another column?? From further discussion, I learned that: total rows in table is 2 million. It'll have unique words of less than 1 million.. (approx ...

Couple of days ago I had a problem that I couldn't solve after ~ 2 hours, and decided to ask on IRC. Almost immediately after asking, I figured out the solution, but David asked me to write about the solution, ...

I got asked on irc to show some examples how to use recursive CTE. Apparently my previous post wasn't good enough :) I think that most of the users will use recursive cte to deal with trees I decided to show ...

Today, on Stack Overflow there was interesting question. Generally, given table that looks like this: room | people | price | hotel 1 | 1 | 200 | ...