Gj’s SQL blog

April 19, 2009

how to speed up index on bytea, text, etc.

Filed under: index, postgresql — Gregg Jaskiewicz @ 5:43 pm


I recently had to create db to store some binary information. Now, these entries were to be unique in their category.

Average size of bytea field was to be 2400 bytes (about 2kb). Everything was fine, until table filled with 1.3M unique entries, and I was really astound how slow insertion become.



on using different types in joins, and performance of it

Filed under: index, joins, postgresql — Gregg Jaskiewicz @ 4:44 pm

I get this question quite often from folks I work with, or people I join in their projects, usually with database designed by someone who is not very experienced in database world.

Most of them, don’t understand why the hell I am pressing for using bigint/int as a key to a table.

Here’s simple example, and my own theory behind it.


The power of Views

Filed under: postgresql, views — Gregg Jaskiewicz @ 11:52 am

So, views.

What are they useful for?  how can we utilize such a power of views ?


Unions and order

Filed under: postgresql, unions — Gregg Jaskiewicz @ 2:45 am

Quick note, on something that might save you few minutes.

It’s about UNION [ALL]. And why you should never assume order of output to be predetermined by order of tables in query.



Filed under: postgresql — Gregg Jaskiewicz @ 2:20 am


I decided to document at least some attempts with SQL here.

I will mainly talk about PostgreSQL, because that’s what I use.


Blog at WordPress.com.