preciated.
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 <[EMAIL PROTECTED]> 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) th
9GHz processors and
2GB of RAM.
effective_cache_size = 100k
shared_buffers = 14k
random_page_cost = 3
default_statistics_target = 50
VACUUM ANALYZE runs every few hours, so statistics should be up to date.
Appreciate any input here.
Cheers
Jona
---(e
Index Cond: ((price_tbl.affid = 8)
AND ("outer".sctid = price_tbl.sctid))
Total runtime: 225.14 msec
It seems that the more it knows about
Could you provide some input on how to make it realise that the plan it
selects is not the optima
Thank you for the insight, any suggestion as to what table / columns I
should compare between the databases?
Cheers
Jona
Dennis Bjorklund wrote:
On Thu, 9 Jun 2005, Jona wrote:
It's the same (physical) server as well as the same PostGreSQL daemon,
so yes.
The
:
- test
- live
Makes sense??
/Jona
Christopher Kings-Lynne wrote:
Is effective_cache_size set the same on the test and live?
Jona wrote:
Thanks... have notified our sys admin of that so he can make the
correct changes.
It still doesn't explain the difference in query plans though?
I
ng the seq scan of the 50k
records in Price_Tbl and the 6.5k records in SCT2SubCatType_Tbl.
Seems weird....
Cheers
Jona
Christopher Kings-Lynne wrote:
Thank you for the swift reply, the following is the output of the
SHOW ALL for shared_buffers and effective_cache_size.
shared_buffe
od plan it uses the indexes available as expected.
The estimated cost is obviously way off in the live database, even
though statistics etc should be up to date. Any insight into this?
Appreciate the help here...
Cheers
Jona
Dennis Bjorklund wrote:
On Thu, 9 Jun 2005 [EMAIL PROTEC
2597
Count for Test Server: 11494
Any insight into this?
Cheers
Jona
PS: The meta data for the table is:
CREATE TABLE statcon_tbl
(
id serial NOT NULL,
data bytea,
wm bool DEFAULT 'FALSE',
created timestamp DEFAULT now(),
modified timestamp DEFAULT now(),
enabled bool DEFAULT
597
Test Server: 11494
When the problems started the tables had identical size though.
Cheers
Jona
Tom Lane wrote:
Jona <[EMAIL PROTECTED]> writes:
Test Server:
comm=# VACUUM ANALYZE VERBOSE StatCon_Tbl;
INFO: --Relation public.statcon_tbl--
INFO: Pages 338: Changed 0, Empt
t used its index.
Cheers
Jona
Christopher Kings-Lynne wrote:
You didn't do analyze.
Chris
Jona wrote:
Results of VACUUM VERBOSE from both servers
Test server:
comm=# VACUUM VERBOSE StatCon_Tbl;
INFO: --Relation public.statcon_tbl--
INFO: Pages 338: Changed 338, Empty 0; Tup 1
0, UnUsed
6101.
Total CPU 0.01s/0.00u sec elapsed 0.60 sec.
INFO: --Relation pg_toast.pg_toast_891830--
INFO: Pages 89234: Changed 0, Empty 0; Tup 352823: Vac 0, Keep 0,
UnUsed 5487.
Total CPU 4.44s/0.34u sec elapsed 35.48 sec.
VACUUM
Cheers
Jona
Tom Lane wrote:
Jona <[EM
00..8.13
rows=2 width=8) (actual time=1.10..2.39 rows=2 loops=69)"
Will get a VACUUM VERBOSE of StatCon_Tbl
Cheers
Jona
PS: The query plans are extracted using pgAdmin on Windows, if you can
recommend a better cross-platform postgre client I'd be happy to try it
out.
Tom Lane wrote:
ies.
If anyone could shed some light on these issues I would truly appreciate
it.
Cheers
Jona
PS. Please refer to part 2 for the other queries and query plans
Query 1:
EXPLAIN ANALYZE
Please refer to part 1a for questions and part 2 for more queries and
query plans.
Why won't this list accept my questions and sample data in one mail???
/Jona
Query 1:
EXPLAIN ANALYZE
S
Please refer to part 1 for question and query 1
Cheers
Jona
---
Query 2:
EXPLAIN ANALYZE
SELECT DISTINCT CatType_Tbl.id, CatType_Tbl.url, Category_Tbl.name,
Min(SubCatType_Tbl.id
Testing list access
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
16 matches
Mail list logo