imitatio creatio

Rafal Pietrak asked a question about postgres performance in partitioning scenarios.

The problem is, in classical partitioning approach you decide into which partition put the data basing only on the inserted data itself.

But we consider also situation when you want to make this decision basing on current database content.

For example we have some “driving” or “routing” table which tells us which partition is currently active.

Please read the above post for more background.

I prepared 4 test cases, for all combinations of rule versus trigger and static versus dynamic aka table-driven partitioning.

Test was performed on PostgreSQL 8.3.3 on Linux, commodity desktop box.

To make things short, here are the results of two test runs (links point to test scripts):
/what is measured: INSERT of 10000 rows/

Partitioning with RULEs, no dynamic routing:
2444.293 ms 2516.314 ms

Partitioning with RULEs, with dynamic routing:
42380.037 ms 39248.666 ms

Partitioning with TRIGGER, no dynamic routing:
14512.787 ms 14669.310 ms

Partitioning with TRIGGER, with dynamic routing:
13486.808 ms 13904.370 ms

Conclusion:

If you have to do some database lookup to decide which partition data belongs to, use a trigger on master table.
If you have a well defined static set of rules, use PostgreSQL rule system.

hstore is a “Lazy DBA” extension for postgres, which enables storing hash tables in a table column.

When using hstore, sometimes you want to rename hash keys.


#
# create function renamekey( _hstore hstore, _oldname text, _newname text ) returns hstore as
$$ select delete($1, $2) || ($3 => ($1 -> $2)) $$ language sql immutable strict;
#
# \set old_codename '''dizzyflag'''
# \set new_codename '''is_trade'''
#
# UPDATE table_with_hstore
SET flags = renamekey(flags, :old_codename, :new_codename)
WHERE (flags -> :old_codename) is not null;
#


Jarek napisał ciekawy artykuł o sortowaniu napisów w PostgreSQL (i nie tylko, w zasadzie rzecz dotyczy localesów w glibc).

Same as depesz and Greg, but using user-defined function:


CREATE FUNCTION ea_totals(text) RETURNS text AS $body$
my $result;
my $com = shift;
$com =~ /^\s*explain\s+/i or die qq{Not an explain query\n};
my $rv = spi_exec_query($com);
$rv->{status} eq 'SPI_OK_UTILITY' or die qq{Not a proper explain?\n};
for (map { $_->{'QUERY PLAN'} } @{$rv->{rows}}) {
my $string = ' ' x 10;
if ( /actual time=\d+\.\d*\.\.(\d+\.\d*) rows=\d+ loops=(\d+)/ ) {
$string = sprintf("%10.1f", $1 * $2);
}
$result .= "$string $_ \n";
}
return $result;
$body$ LANGUAGE plperl;


Usage:

SELECT * FROM ea_totals( $$ explain analyze query here $$);


Funkcje odpytywania tekstów w standardzie SQL

Ponieważ w pracy zajmuję się ostatnio trochę wyszukiwaniem pełnotekstowym (zwłaszcza dodatkiem tsearch2 do PostgreSQL), zastanawiałem się, czy doczekamy się kiedyś powszechnego standardu SQL dla odpytywania tekstów.

Tak żeby znający SQL człowiek, który podchodzi do swojego pięknego nowiutkiego “wypasionego” serwera nie musiał się zastanawiać co napisać, aby zmusić go do czarnej roboty.

Okazuje się, że już od jakiegoś czasu istnieje standard SQL/MM, który m.in. opisuje rozszerzenia pełnotekstowe.

Podaję przykłady zapytań, bo mówią same za siebie.

Tworzymy tabelę z polem pełnotekstowym:
CREATE TABLE dokumenty (
id INTEGER,
body FULLTEXT
)

Szukamy dokumentów, w których wyraz brzmiący podobnie do parboiled pojawia się w tym samym zdaniu, co słowo rice:
SELECT id
FROM dokumenty
WHERE body.CONTAINS(
' SOUNDS LIKE "parboiled"
IN SAME SENTENCE AS "rice" '
)

Szukamy dokumentów zawierających terminy bliskoznaczne do kontrola błędów (np. obsługa wyjątków):
SELECT id
FROM dokumenty
WHERE body.CONTAINS(
' THESAURUS "informatyka" EXPAND SYNONYM TERM OF "kontrola błędów" '
)

Standard definiuje też konstrukcje, których implementacja jest co najmniej nietrywialna:
SELECT id FROM dokumenty
WHERE body.CONTAINS( ' IS ABOUT "analiza leksykalna" ' )
ORDER BY body.SCORE( ' IS ABOUT "analiza leksykalna" ' )


Ładne, prawda? No w każdym razie dla mnie wygląda to bardzo sympatycznie.
Zachęcam do zerknięcia na cały dokument, jest do pobrania na stronie www.wiscorp.com.

Jednak nie wpadajmy w euforię.

Po pierwsze, SQL/MM Full-Text na razie jest tylko w ułamkowej części implementowany przez niektóre silniki bazodanowe.
Po drugie, relacyjne bazy danych mają swoje ograniczenia. Widoczne są one także na polu wyszukiwania “ze zrozumieniem” - bo w sumie do tego dążymy jak się chwilę zastanowić. Pisze o tym np. Curt Monash w artykule Relational DBMS versus text data.