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.
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 🙂
faber4 on irc asked about how to get ascii-based sorting, while his postgresql was initdb'ed with utf-8 based locale (en_US.UTF-8 to be exact).
what can we do about it?
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.
luckymurali_81 on freenodes #postgresql had a problem. his query returns data in wrong order. what can we do about it?
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ć 🙂
Bugfixes:
Changes:
SVN repo at: http://svn.depesz.com/svn/analyze.pgsql.logs/trunk
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:
reason? very “interesting" orm.
now i'll show you how i deal with these kind of situations 🙂
now, some time ago i wrote about how to “hack" system with postgresql. today i'll write more about how to make pg database as safe as possible.
i'll go through several steps, tell you what's possible, what's easy, and what is not so easy 🙂
so, let's start the tutorial.
Continue reading securing your postgresql database
one of the nice (for some things) features of oracle is rownum() function.
i assume you know what it does – if you don't – i think a quick google will show it.
today i will show how to make rownum in postgresql. if you're not interested in rownum itself – please continue reading anyway, as some functions shown here have other uses as well 🙂
Continue reading rownum anyone? cumulative sum in one query?