I have a query that I think should run faster. The machine is P2/400 with enough ram (384MB), but still, maybe the query could be tuned up.
postgresql.conf is stock with these values changed:

shared_buffers = 5000
sort_mem = 8192
vacuum_mem = 16384

This is a development machine, the production will be dual P3, 1GHz, 1GB RAM, but I fear that the execution will still be slow, as the tables will get bigger.

I've pasted information about the database, and the explain output, but the text is horribly wrapped so there's a clean copy on the web in http://geri.cc.fer.hr/~ivoras/query.txt

The intention is: there is a table called cl_log which records events from various sources, some of which also have data in data_kat_id and data_user_id fields, some of which don't (hence the outer joins). The query is report-style, and tries to collect as much data as possible about the events. Tables cl_source, cl_handler and cl_event_type hold information about the type of event. They are small (currently 1-3 records in each, will grow to about 10 records).

ferweb=> explain analyze SELECT cl_log.*, cl_source.name AS source_name, cl_source.description AS source_description,
cl_handler.name AS handler_name, cl_handler.description AS handler_description, cl_event_type.name AS event_type_name,
cl_event_type.description as event_type_description, users.jime, kategorija.knaziv
FROM cl_log
INNER JOIN cl_source ON source_id=cl_source.id
INNER JOIN cl_handler ON cl_source.handler_id=cl_handler.id
INNER JOIN cl_event_type ON event_type_id=cl_event_type.id
LEFT OUTER JOIN kategorija ON data_kat_id=kategorija.id
LEFT OUTER JOIN users ON data_user_id=users.id
ORDER BY time desc LIMIT 30;

Limit (cost=788.78..788.79 rows=2 width=500) (actual time=23229.78..23230.44 rows=30 loops=1)
-> Sort (cost=788.78..788.79 rows=3 width=500) (actual time=23229.75..23230.10 rows=31 loops=1)
Sort Key: cl_log."time"
-> Nested Loop (cost=1.04..788.76 rows=3 width=500) (actual time=4078.85..20185.89 rows=38999 loops=1)
-> Nested Loop (cost=1.04..771.27 rows=3 width=485) (actual time=4078.71..14673.27 rows=38999 loops=1)
-> Hash Join (cost=1.04..754.21 rows=3 width=417) (actual time=4078.54..8974.08 rows=38999 loops=1)
Hash Cond: ("outer".event_type_id = "inner".id)
-> Nested Loop (cost=0.00..752.16 rows=195 width=288) (actual time=4078.20..6702.17 rows=38999 loops=1)
Join Filter: ("inner".handler_id = "outer".id)
-> Seq Scan on cl_handler (cost=0.00..1.01 rows=1 width=104) (actual time=0.02..0.04 rows=1 loops=1)
-> Materialize (cost=748.72..748.72 rows=195 width=184) (actual time=4078.08..4751.52 rows=38999 loops=1)
-> Nested Loop (cost=0.00..748.72 rows=195 width=184) (actual time=0.21..3197.16 rows=38999 loops=1)
-> Seq Scan on cl_source (cost=0.00..1.01 rows=1 width=108) (actual time=0.05..0.06 rows=1 loops=1)
-> Index Scan using cl_log_source on cl_log (cost=0.00..745.27 rows=195 width=76) (actual time=0.11..1467.08 rows=38999 loops=1)
Index Cond: (cl_log.source_id = "outer".id)
-> Hash (cost=1.03..1.03 rows=3 width=129) (actual time=0.12..0.12 rows=0 loops=1)
-> Seq Scan on cl_event_type (cost=0.00..1.03 rows=3 width=129) (actual time=0.04..0.08 rows=3 loops=1)
-> Index Scan using kategorija_pkey on kategorija (cost=0.00..5.82 rows=1 width=68) (actual time=0.05..0.07 rows=1 loops=38999)
Index Cond: ("outer".data_kat_id = kategorija.id)
-> Index Scan using users_pkey on users (cost=0.00..5.97 rows=1 width=15) (actual time=0.05..0.07 rows=1 loops=38999)
Index Cond: ("outer".data_user_id = users.id)
Total runtime: 23267.25 msec
(22 rows)

ferweb=> select count(*) from cl_log;
(1 row)

ferweb=> select count(*) from cl_handler;
(1 row)

ferweb=> select count(*) from cl_source;
(1 row)

ferweb=> select count(*) from cl_event_type;
(1 row)

ferweb=> select count(*) from users;
(1 row)

ferweb=> select count(*) from kategorija;
(1 row)

Every sufficiently advanced magic is indistinguishable from technology
   - Arthur C Anticlarke

---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly

Reply via email to