this topic has been written about by many smart people – from the recent past, by greg sabino mullane and josh berkus.
they show 4 different approaches:
all these approaches have their benefits and drawbacks, but i'd like to show another one (polish readers saw the approach already in january 2007, but this time i will make the code more robust).
as most of you know postgresql can easily speedup searches using:
field like 'something%'
and (less easily):
field like '%something'
but how about:
field like '%something%'
general idea is to use some kind of full text search/indexing – tsearch, lucene, sphinx, you name it.
but sometimes you can't install fts/fti, or it doesn't really solve your problem. is there any help? let's find out.
so there you go, you have some “categories" and some objects. for simplicity let's assume one object can be in only one category.
if this is too theoretical for you – let's assume these are “mails in folders", “photos in galleries", “posts in categories" or “auctions in categories on ebay".
everything clear? now, let's assume you want to know how many “objects" are in given “category".
most basic way to do it is:
SELECT COUNT(*) FROM objects WHERE category = some_category;
but this method is far from optimal. now, we'll learn how to do it better.
one warning for those of you who read the rss feed – if you say “yeah, i know the code, it's simple" – ask yourself – is your code deadlock-proof?
Continue reading objects in categories – counters with triggers
new commit info from cvs:
Log Message: ----------- Stamp releases 8.2.5, 8.1.10, 8.0.14, 7.4.18, 7.3.20.
it doesn't mean that we will see them today, but it means that we should be getting them really soon now 🙂 (at least that's what i hope)
znajomy zadzwonił i poprosił bym mu nagrał jakiś serial w tv.
nagrałem i w trakcje oglądania spojrzałem na reklamy. pojawiła się reklama “vision express" gdzie dają zniżki procentowo takie ile ma się lat.
reklama jak reklama, ale to co mnie całkiem powaliło to wygląd tej planszy do sprawdzania wzroku (litery różnej wielkości).
oto 2 “screenshoty" z tej reklamy:
drugie zdjęcie jest nieostre (kamera w reklamie była w ruchu), ale pokazuję, że na górze jest “O".
powstaje pytanie – czy ten co zatwierdzał reklamę zwrócił uwagę na żarcik? z treści reklamy nie wynika by miała być ona “z przymrużeniem oka".
just recently i saw a very interesting situation.
one table in customer database (with about 1.5 milion rows) was *very often* updated. using series of simple:
UPDATE TABLE SET FIELD = .., WHERE id = ...
updates always updated 1 record, search was using primary key, so it was quite fast.
what was strange was the fact that the table get about 20 times more updates then the next most updated table.
after careful checking i found out that a lot (about 60-70%) of the updates actually didn't change anything!
they were simply setting values that were already there.
so, i started to think about hwo to avoid this kind of situation.
and this is what i found out.
Bugfixes:
Changes:
SVN repo at: http://svn.depesz.com/svn/pgsql-tools/trunk/
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.
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 🙂