(postgre)sql gotchas

i would like to write about a things that are not really errors by themselves – they are simply situations when database acts in undesired way. a way that's perfectly logical, but where the logic is not always clearly seen “at first sight".

so, don't expect any postgresql-bashing. if you want some, indicate so in comments, i'll find some things to bash postgresql for.

but in here i'd rather bash us – people – for making mistakes. the ones that are not really easy to see as mistakes for database.

Continue reading (postgre)sql gotchas

indexable “field like ‘%something'” – update

thomas reiss just published his follow up to my post about indexed searches in “like ‘%something'" cases.

i don't understand french so i can't tell what exactly he wrote, but i understand c code and graphs 🙂

basically – he wrote c function that does reverse() of strings, and benchmarked it against pl/pgsql and pl/perl versions. of course c version is the fastest, but just take a look at how much faster it is. and how simple it is.

how many transactions per second?

i wanted to know how many transactions per second is my machine processing.

how to do so? a simple select to pg_stat_database will do the job (actually 2 selects 🙂

but since i have to write it anyway, perhaps i can/should make it so it will print the current value continuously?

and, while i'm at it, some kind of graph wouldn't be bad 🙂

Continue reading how many transactions per second?

better results paging in postgresql 8.2

some time ago merlin moncure wrote about one of new features of postgresql 8.2 – row-wise comparison.

i also read about it, but at first didn't find anything very useful about it – after all it doesn't give you any new functionality. any kind “(a, b, c) > (…)" can be written with standard column-based operators. so it's basically just a syntactic-sugar.

that's true. but merlin pointed me to not-so-obvious benefit – basically it allows much better paging of results. how? let's see.

Continue reading better results paging in postgresql 8.2

łosoś w sake [polish only]

ostatnio testowałem interesujący i dosyć prosty pomysł na jedzonko.

zaczęło się od tego, że przypadkiem usłyszałem w telewizji opis jakiejś potrawy: “to jest pieczona ryba (tu była jakaś nazwa, ale jej nie znam) marynowana w sake z syropem klonowym, zwróćcie uwagę na ten wytopiony tłuszcz – ryba musi był tłusta by się potrawa udała". i to było wszystko co usłyszałem. nie znałem proporcji, czasu, temperatury, nic.

ale – zawsze można poeksperymentować 🙂

Continue reading łosoś w sake [polish only]

analyze.pgsql.logs

Bugfixes:

  1. set locale to C for sorting – otherwise sort results will not be really sorted (let's everybody “thank" locale subsystem)
  2. fix a bug that prevented *last* sql-type to be printed

Changes:

  1. add rule for normalizing so: SELECT * FROM table WHERE field = -12 will be normalized to SELECT * FROM table WHERE field = ? instead of “… field = – ?"
  2. remove extra spaces from normalized version – they did happen sometimes 🙁

SVN repo at: http://svn.depesz.com/svn/analyze.pgsql.logs/trunk

effective finding queries to optimize

let's imagine simple situation – you have postgresql server. configuration was fine-tuned, hardware is ok. yet the system is not really as fast as it should.

most common problem – slow queries.

second most common problem – fast queries, but too many of them. for example – i once saw a system which did something like this:

  • select id from table;
  • for every id do:
  • select * from table where id = ?

reason? very “interesting" orm.

now i'll show you how i deal with these kind of situations 🙂

Continue reading effective finding queries to optimize