Tag: postgresql

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

rownum anyone? cumulative sum in one query?

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?

“hacking” with postgresql

< french translation is now available >

very recently i got a task which involved finding out what happened (what sql queries were called) on remote machine.

the problem was that i didn't have any kind of shell access to server – i could connect only to postgresql. the good side was that i had a superuser-level access.

so the task was quite simple – how to find and read a file from psql. on remote machine. with no access to ssh, ftp, nor anything like this.

this is when i got idea that i'll write more about it. some might say that you shouldn't write this kind of information. my view is that i'm not disclosing any secrets – i will be using only basic (remote) psql with superuser access. all things that i will describe in here are in documentation – you just have to know where to look for them.

Continue reading “hacking" with postgresql

psql prompt issue

i tend to use more (than standard) informative prompts.

whether it's shell, or psql i set it up to match my needs.

for the longest time i used psql prompt:

\set PROMPT1 '(%n@%M:%>) %`date +%H:%M:%S` [%/] %[%012%]%x%# '

(this line should be in .psqlrc).

it gives some information:

(depesz@[local]:5810) 15:26:06 [depesz]
#

at a glance i know what user i am conencted as, to which machine, over which port, what database i'm connected to, and what's current time.

unfortunately this prompt had one issue – whenever i used history, and then something like <ctrl-a> (go to begining of line) something weird happened – cursor got distorted, and it looked like it was on second letter, while in fact it was on first (hard to explain, try it for yourself :).

it was painful, but since i couldn't do anything about it, i just learned to live with it.

now, due to some new environment setup, i decided that i've got enough of it. i tried to find any help in manual (i was looking for something like \[ and \] from bash prompting), but to no avail.

i did find out that the problem lies in %[012%]. i tried to give there literal <enter> (thus making \set statement multi-line), but it didn't work.

and then i simply gave there standard “\n". and it was it – now i have the prompt the way i like it, with no side effects on command line edition.

final prompt1, in case you're interested:

\set PROMPT1 '(%n@%M:%>) %`date +%H:%M:%S` [%/] \n%x%# '

simple howto about restoring damaged template1

this is nothing new, but i blog it to have a place to point people to.

let's assume you accidentally loaded dump file in template1 database.

this is definitely not something one could have wanted (i mean i can see some uses for this but it's not really likely).

so, now you want to cleanup your template1.

how? that's easy.

connect with your superuser account (postgres) to some database other than template1.

and then issue these queries:

  1. # update pg_database set datistemplate = false where datname = ‘template1';
  2. # drop database template1;
  3. # create database template1 with template template0;
  4. # update pg_database set datistemplate = true where datname = ‘template1';

be sure that there is no connection to template1 at the moment you're dealing with it.

and that's all. nothing really complicated.

set returning functions in 8.3

just couple of days ago i read about a new, great addition to postgresql 8.3 – “return query" in pl/pgsql.

what does it do?

in set returning functions, when you wanted to return multiple rows from a given query you had to:

FOR record IN SELECT ..... LOOP
    RETURN NEXT record;
END LOOP;

now, you can simply:

RETURN QUERY SELECT ...;

what's more – since RETURN QUERY doesn't terminate function (just like return next) you can:

RETURN QUERY SELECT something;
RETURN QUERY SELECT something ELSE;

and then you'll get (more or less) “union all" of the queries.

additionally – return query is supposed to be faster then return next/loop.

so, let's test it.
Continue reading set returning functions in 8.3

indexable ” field like ‘%something'”

for the long time everybody knew that you can't use index on “LIKE" operations.

then came text_pattern_ops, so we could use indexes for prefix searches:

# \d depesz_test
                         Table "public.depesz_test"
 Column |  Type   |                        Modifiers
--------+---------+----------------------------------------------------------
 id     | integer | not null default nextval('depesz_test_id_seq'::regclass)
 email  | text    | not null
Indexes:
    "depesz_test_pkey" PRIMARY KEY, btree (id)
    "x" UNIQUE, btree (email text_pattern_ops)
# EXPLAIN analyze SELECT COUNT(*) FROM depesz_test WHERE email LIKE 'dep%';
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=96.71..96.72 ROWS=1 width=0) (actual TIME=0.983..0.985 ROWS=1 loops=1)
   ->  Bitmap Heap Scan ON depesz_test  (cost=4.68..96.65 ROWS=24 width=0) (actual TIME=0.184..0.641 ROWS=155 loops=1)
         FILTER: (email ~~ 'dep%'::text)
         ->  Bitmap INDEX Scan ON x  (cost=0.00..4.67 ROWS=24 width=0) (actual TIME=0.158..0.158 ROWS=155 loops=1)
               INDEX Cond: ((email ~>=~ 'dep'::text) AND (email ~<~ 'deq'::text))
 Total runtime: 1.067 ms
(6 ROWS)

but what if i'd like to search for ‘%something'? not prefix, but suffix. in my example – what can i do to use indexes when searching for people from given domain?

Continue reading indexable " field like ‘%something'"