Re: How to enhance the chance that data is in disk cache

Thank you for the response Tom, I bet you get a lot of mails with
"trivial" solutions (mine likely being one of them)
I for one however truly appreciate you taking the time to answer them.

>Run the query more often?
>
>
The query is dynamically constructed from user input, although the total
number of different queries that can be run is limited (around 10k
different combinations I suspect) it seems rather pointless to run all
of them (or even the most common) more often just to keep the data in
the disk cache.
Is there a way to make the data more accessible on the disk?

>Also, that pile of INNER JOINs is forcing a probably-bad join order;
>you need to think carefully about the order you want things joined in,
>or else convert the query to non-JOIN syntax. See the "Performance
>Tips" chapter of the manual.
>
>
You're probably right here, the join order must be bad though it just
flattening the join and letting the planner decide on what would be best
makes the plan change for every execution.
Have query cost variering from from 1350 to 4500.
I wager it ends up using GEQO due to the number of possiblities for a
join order that the query has and thus just decides on a "good" plan out
of those it examined.
In any case, the "right" way to do this is definning a good explicit
join order, no?
On top of my head I'm not sure how to re-write it proberly, suppose
trial and errors is the only way....
From the plan it appears that the following part is where the cost
dramatically increases (although the time does not??):
-> Nested Loop (cost=0.00..1207.19 rows=75 width=32) (actual
time=0.28..18.47 rows=164 loops=1)
-> Nested Loop (cost=0.00..868.23 rows=58 width=20) (actual
time=0.16..13.91 rows=164 loops=1)
-> Index Scan using subcat_uq on sct2subcattype_tbl
(cost=0.00..479.90 rows=82 width=8) (actual time=0.11..9.47 rows=164
loops=1)
Index Cond: (subcattpid = 50)
Filter: (NOT (subplan))
SubPlan
-> Seq Scan on aff2sct2subcattype_tbl (cost=0.00..1.92
rows=1 width=4) (actual time=0.05..0.05 rows=0 loops=164)
Filter: ((affid = 8) AND ($0 = sctid))
-> Index Scan using aff_price_uq on price_tbl (cost=0.00..4.72
rows=1 width=12) (actual time=0.02..0.02 rows=1 loops=164)
Index Cond: ((price_tbl.affid = 8) AND (price_tbl.sctid =
outer".sctid))"
-> Index Scan using ctp_statcon on statcon_tbl (cost=0.00..5.86
rows=1 width=12) (actual time=0.02..0.02 rows=1 loops=164)
Index Cond: ((statcon_tbl.sctid = outer".sctid) AND
(statcon_tbl.ctpid = 1))"
Especially the index scan on subcat_uq seems rather expensive, but is
pretty fast.
Can there be drawn a relation between estimated cost and execution time?
Any other pointers in the right direction would be very much appreciated.

For the full query and query plan, please refer to:
http://213.173.234.215:8080/get_content_plan.htm

Cheers
Jona

Tom Lane wrote:

>Jona <jonanews(at)oismail(dot)com> writes:
>
>
>>I have a query (please refer to
>>http://213.173.234.215:8080/get_content_plan.htm for the query as well
>>as query plan) that is slow when it's run the first time and fast(ish)
>>on all successive runs within a reasonable time period.
>>
>>
>
>
>
>>This leads me to suspect that when the query is first run, all used data
>>have to be fetched from the disk where as once it has been run all data
>>is available in the OS's disk cache.
>>
>>
>
>Sounds like that to me too.
>
>
>
>>Is there anway to either enhance the chance that the data can be found
>>in the disk cache or allowing the database to fetch the data faster?
>>
>>
>
>
>

>Run the query more often?
>
>
The query is dynamically constructed from user input, although the total
number of different queries that can be run is limited (around 10k
different combinations I suspect) it seems rather pointless to run all
of them (or even the most common) more often just to keep the data in
the disk cache.
Is there a way to make the data more accessible on the disk?

>Also, that pile of INNER JOINs is forcing a probably-bad join order;
>you need to think carefully about the order you want things joined in,
>or else convert the query to non-JOIN syntax. See the "Performance
>Tips" chapter of the manual.
>
>
You're probably right herem though I'm not sure I can

> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>