Hi, All!


Richard Huxton wrote:


On Thursday 07 August 2003 17:30, Yaroslav Mazurak wrote:

Richard Huxton wrote:

On Thursday 07 August 2003 09:24, Yaroslav Mazurak wrote:

PG's memory use can be split into four areas (note - I'm not a developer so this could be wrong).
1. Shared memory - vital so that different connections can communicate with each other. Shouldn't be too large, otherwise PG spends too long managing its shared memory rather than working on your queries.
2. Sort memory - If you have to sort results during a query it will use up to the amount you define in sort_mem and then use disk if it needs any more. This is for each sort.
3. Results memory - If you're returning 8000 rows then PG will assemble these and send them to the client which also needs space to store the 8000 rows.
4. Working memory - to actually run the queries - stack and heap space to keep track of its calculations etc.

Hence, total free RAM - shared_buffers - k * sort_mem - effective_cache_size == (results memory + working memory)?


For the moment, I'd leave the settings roughly where they are while we look at the query, then once that's out of the way we can fine-tune the settings.

OK.


        Additionally, with user-defined function beginchar (SUBSTR(..., 1, 1)),
used for indexing, showcalc executes about 16 seconds. With function
SUBSTR the same showcalc executes 6 seconds.


Fair enough - substr should be fairly efficient.

Cost of user-defined SQL function call in PostgreSQL is high?


OK - bear in mind that these suggestions are made without the benefit of the explain analyse:

1. You could try splitting out the various tags of your mask into different fields - that will instantly eliminate all the substr() calls and might make a difference. If you want to keep the mask for display purposes, we could build a trigger to keep it in sync with the separate flags.

This will be next step. :)


2. Use a "calculations" table and build your results step by step. So - calculate all the simple accounts, then calculate the ones that contain the simple accounts.

I give to SQL to user and few helper functions. Therefore single step is required for building results.


3. You could keep a separate "account_contains" table that might look like:
  acc_id | contains
  A001   | A001
  A002   | A002
  A003   | A003
  A003   | A001
  A004   | A004
  A004   | A003
  A004   | A001

So here A001/A002 are simple accounts but A003 contains A001 too. A004 contains A003 and A001. The table can be kept up to date automatically using some triggers.
This should make it simple to pick up all the accounts contained within the target account and might mean you can eliminate the recursion.

Thanks, sounds not so bad, but I suspect that this method don't improve performance essentially.
I think about another secondary table for showcomp (compshow :)) with showings "compiled" into account numbers and characteritics. After inserting or updating new or old showing this showing will be "recompiled" by explicit function call or trigger into atomary account numbers and characteristics.


Post the EXPLAIN ANALYSE first - maybe someone smarter than me will have an idea.

First result - simple showing 'B00202' (without recursion).
Second result - complex showing 'B00204' with recursion (1 level depth).
Showing 'B00202' contains 85 accounts, 'B00203' - 108 accounts, and 'B00204' = 'B00202' - 'B00203'.
Query text:


EXPLAIN ANALYZE SELECT COALESCE(
(SELECT sc.koef * 100
FROM showing AS s NATURAL JOIN showcomp AS sc
WHERE s.kod = 'B00202'
AND NOT SUBSTR(acc_mask, 1, 1) = '['
AND SUBSTR(acc_mask, 1, 4) = '6010'
AND SUBSTR(acc_mask, 5, 1) = SUBSTR('20', 1, 1)),
(SELECT SUM(sc.koef * COALESCE(showcalc(SUBSTR(acc_mask, 2, LENGTH(acc_mask) - 2), '20', '6010', 100), 0))
FROM showing AS s NATURAL JOIN showcomp AS sc
WHERE s.kod = 'B00202'
AND SUBSTR(acc_mask, 1, 1) = '['),
0) AS showing;


EXPLAIN ANALYZE SELECT COALESCE(
(SELECT sc.koef * 100
FROM showing AS s NATURAL JOIN showcomp AS sc
WHERE s.kod = 'B00204'
AND NOT SUBSTR(acc_mask, 1, 1) = '['
AND SUBSTR(acc_mask, 1, 4) = '6010'
AND SUBSTR(acc_mask, 5, 1) = SUBSTR('20', 1, 1)),
(SELECT SUM(sc.koef * COALESCE(showcalc(SUBSTR(acc_mask, 2, LENGTH(acc_mask) - 2), '20', '6010', 100), 0))
FROM showing AS s NATURAL JOIN showcomp AS sc
WHERE s.kod = 'B00204'
AND SUBSTR(acc_mask, 1, 1) = '['),
0) AS showing;


QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0) (actual time=704.39..704.39 rows=1 loops=1)
InitPlan
-> Hash Join (cost=5.22..449.63 rows=1 width=19) (actual time=167.28..352.90 rows=1 loops=1)
Hash Cond: ("outer".id_show = "inner".id_show)
-> Seq Scan on showcomp sc (cost=0.00..444.40 rows=1 width=15) (actual time=23.29..350.17 rows=32 loops=1)
Filter: ((substr((acc_mask)::text, 1, 1) <> '['::text) AND (substr((acc_mask)::text, 1, 4) = '6010'::text) AND (substr((acc_mask)::text, 5, 1) = '2'::text))
-> Hash (cost=5.22..5.22 rows=1 width=4) (actual time=0.67..0.67 rows=0 loops=1)
-> Index Scan using index_showing_kod on showing s (cost=0.00..5.22 rows=1 width=4) (actual time=0.61..0.64 rows=1 loops=1)
Index Cond: (kod = 'B00202'::character varying)
-> Hash Join (cost=5.22..449.63 rows=1 width=19) (actual time=166.20..351.28 rows=1 loops=1)
Hash Cond: ("outer".id_show = "inner".id_show)
-> Seq Scan on showcomp sc (cost=0.00..444.40 rows=1 width=15) (actual time=23.36..349.24 rows=32 loops=1)
Filter: ((substr((acc_mask)::text, 1, 1) <> '['::text) AND (substr((acc_mask)::text, 1, 4) = '6010'::text) AND (substr((acc_mask)::text, 5, 1) = '2'::text))
-> Hash (cost=5.22..5.22 rows=1 width=4) (actual time=0.17..0.17 rows=0 loops=1)
-> Index Scan using index_showing_kod on showing s (cost=0.00..5.22 rows=1 width=4) (actual time=0.12..0.14 rows=1 loops=1)
Index Cond: (kod = 'B00202'::character varying)
-> Aggregate (cost=312.61..312.61 rows=1 width=28) (never executed)
-> Hash Join (cost=5.22..312.61 rows=1 width=28) (never executed)
Hash Cond: ("outer".id_show = "inner".id_show)
-> Seq Scan on showcomp sc (cost=0.00..307.04 rows=69 width=24) (never executed)
Filter: (substr((acc_mask)::text, 1, 1) = '['::text)
-> Hash (cost=5.22..5.22 rows=1 width=4) (never executed)
-> Index Scan using index_showing_kod on showing s (cost=0.00..5.22 rows=1 width=4) (never executed)
Index Cond: (kod = 'B00202'::character varying)
-> Aggregate (cost=312.61..312.61 rows=1 width=28) (never executed)
-> Hash Join (cost=5.22..312.61 rows=1 width=28) (never executed)
Hash Cond: ("outer".id_show = "inner".id_show)
-> Seq Scan on showcomp sc (cost=0.00..307.04 rows=69 width=24) (never executed)
Filter: (substr((acc_mask)::text, 1, 1) = '['::text)
-> Hash (cost=5.22..5.22 rows=1 width=4) (never executed)
-> Index Scan using index_showing_kod on showing s (cost=0.00..5.22 rows=1 width=4) (never executed)
Index Cond: (kod = 'B00202'::character varying)
Total runtime: 706.82 msec
(33 rows)



QUERY PLAN


------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0) (actual time=6256.20..6256.21 rows=1 loops=1)
InitPlan
-> Hash Join (cost=5.22..449.63 rows=1 width=19) (actual time=357.43..357.43 rows=0 loops=1)
Hash Cond: ("outer".id_show = "inner".id_show)
-> Seq Scan on showcomp sc (cost=0.00..444.40 rows=1 width=15) (actual time=23.29..355.41 rows=32 loops=1)
Filter: ((substr((acc_mask)::text, 1, 1) <> '['::text) AND (substr((acc_mask)::text, 1, 4) = '6010'::text) AND (substr((acc_mask)::text, 5, 1) = '2'::text))
-> Hash (cost=5.22..5.22 rows=1 width=4) (actual time=0.22..0.22 rows=0 loops=1)
-> Index Scan using index_showing_kod on showing s (cost=0.00..5.22 rows=1 width=4) (actual time=0.16..0.19 rows=1 loops=1)
Index Cond: (kod = 'B00204'::character varying)
-> Hash Join (cost=5.22..449.63 rows=1 width=19) (never executed)
Hash Cond: ("outer".id_show = "inner".id_show)
-> Seq Scan on showcomp sc (cost=0.00..444.40 rows=1 width=15) (never executed)
Filter: ((substr((acc_mask)::text, 1, 1) <> '['::text) AND (substr((acc_mask)::text, 1, 4) = '6010'::text) AND (substr((acc_mask)::text, 5, 1) = '2'::text))
-> Hash (cost=5.22..5.22 rows=1 width=4) (never executed)
-> Index Scan using index_showing_kod on showing s (cost=0.00..5.22 rows=1 width=4) (never executed)
Index Cond: (kod = 'B00204'::character varying)
-> Aggregate (cost=312.61..312.61 rows=1 width=28) (actual time=2952.69..2952.69 rows=1 loops=1)
-> Hash Join (cost=5.22..312.61 rows=1 width=28) (actual time=12.59..264.69 rows=2 loops=1)
Hash Cond: ("outer".id_show = "inner".id_show)
-> Seq Scan on showcomp sc (cost=0.00..307.04 rows=69 width=24) (actual time=0.09..251.52 rows=1035 loops=1)
Filter: (substr((acc_mask)::text, 1, 1) = '['::text)
-> Hash (cost=5.22..5.22 rows=1 width=4) (actual time=0.17..0.17 rows=0 loops=1)
-> Index Scan using index_showing_kod on showing s (cost=0.00..5.22 rows=1 width=4) (actual time=0.12..0.14 rows=1 loops=1)
Index Cond: (kod = 'B00204'::character varying)
-> Aggregate (cost=312.61..312.61 rows=1 width=28) (actual time=2945.79..2945.80 rows=1 loops=1)
-> Hash Join (cost=5.22..312.61 rows=1 width=28) (actual time=12.02..263.63 rows=2 loops=1)
Hash Cond: ("outer".id_show = "inner".id_show)
-> Seq Scan on showcomp sc (cost=0.00..307.04 rows=69 width=24) (actual time=0.09..251.09 rows=1035 loops=1)
Filter: (substr((acc_mask)::text, 1, 1) = '['::text)
-> Hash (cost=5.22..5.22 rows=1 width=4) (actual time=0.17..0.17 rows=0 loops=1)
-> Index Scan using index_showing_kod on showing s (cost=0.00..5.22 rows=1 width=4) (actual time=0.12..0.14 rows=1 loops=1)
Index Cond: (kod = 'B00204'::character varying)
Total runtime: 6257.35 msec
(33 rows)


        Anyway, 600Mb is too low for PostgreSQL for executing my query - DBMS
raise error after 11.5 hours (of estimated 13?). :(

I think the problem is the 13 hours, not the 600MB. Once we've got the query running in a reasonable length of time (seconds) then the memory requirements will go down, I'm sure.

OK, that's right.



With best regards Yaroslav Mazurak.


---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings

Reply via email to