PostgreSQL performance tuning

[jam231]

So I've just profiled the procedure for adding new sell order (zlec_sprzedaz) with amount=4016, limit1=1 and for 4016 matching buy orders (each with limit1 =1, amount=1) it took about 5 seconds (!) to complete.

Preliminary benchmarks with reactive_sbc@sia-rAgents/Benchmarking look very poorly with ~250 RPS.

[kaiks]

For what it's worth, I'm guessing it might have something to do with adding indices on the orders themselves. Rearranging the index tree might be a bottleneck :-)

[jam231]

Could you elaborate on how to rearrange those indices to better fit the specifics of mentioned queries ? After fixing #76 it improved to ~400 RPS, but it's still rather low.

[kaiks]

I would try running the profiler without indices altogether. I might be wrong, though.

[jam231]

Well, I've read that the query planner decides whether to use indices or not. However I will remove the indices (btw. it seems the proper term is indexes - vide pg_stat_all_indexes), run the benchmark and report back.

Unfortunately the analyze explain doesn't really work on stored procedures in postgres... I only have some vague idea about how the said indices (indexes!) are being used from querying statistics collector's pg_stat_all_indexes.

[jam231]

Ok, so I've removed the indexes and performance degraded to ~330 RPS.

Independently, I've tried to gain some performance by making part of zlec_sprzedaz/zlec_kupno procedure asynchronous (by using dblink_send_query), but the performance instead of improving degraded to ~310 RPS.

[kaiks]

Cool, looks like I was wrong. 30% decrease in performance is significant, I think. Sorry for wasting your time!