re about 1.5
GB each.
Thanks.
-Nick
-----
Nick Fankhauser
[EMAIL PROTECTED] Phone 1.765.965.7363 Fax 1.765.962.9788
doxpop - Court records at your fingertips - http://www.doxpop.com/
--
> As a question, what does explain analyze give you if you
> set enable_nestloop=false; before trying the query?
Here are the results- It looks quite a bit more painful than the other plan,
although the wall time is in the same ballpark.
alpha=# explain analyze
alpha-# select
alpha-# min(a
> It seems that your basic problem is that you're fetching lots of rows
> from two big ol' tables.
> It doesn't seem to me that there would be a substantially better plan
> for this query with your tables as they stand.
That's more or less the conclusion I had come to. I was just hoping someone
e
> The fact that it's taking you 9ms to do each index lookup
> suggests to me that
> it's going to disk each time. Does that sound plausible, or do
> you think you
> have enough RAM to cache your large indexes?
I'm sure we don't have enough RAM to cache all of our large indexes, so your
suppositio
4..9.15
rows=1 loops=3639)
Index Cond: ("outer".actor_id =
actor_summary.actor_id)
Total runtime: 48851.85 msec
(18 rows)
-
Nick Fankhauser
[EMAIL PROTECTED] Phone 1.765.965.7363 Fax 1.765.962.9788
doxpop - Court records at your finger
4..9.15
rows=1 loops=3639)
Index Cond: ("outer".actor_id =
actor_summary.actor_id)
Total runtime: 48851.85 msec
(18 rows)
-
Nick Fankhauser
[EMAIL PROTECTED] Phone 1.765.965.7363 Fax 1.765.962.9788
doxpop - Court records at your fingerti
> It would be interesting to see exactly what inputs are going into this
> equation. Do you feel like adding some debug printouts into this code?
> Or just looking at the variables with a debugger? In 7.3 it's about
> line 1060 in src/backend/commands/analyze.c.
Tom-
I don't really have time
Hi-
I'm seeing estimates for n_distinct that are way off for a large table
(8,700,000 rows). They get better by setting the stats target higher, but
are still off by a factor of 10 with the stats set to 1000. I've noticed and
reported a similar pattern before on another table. Because this follows
> Does actor_case_assignment contain more columns than just the two ids?
> If yes, do these additional fields account for ca. 70 bytes per tuple?
> If not, try
> VACUUM FULL ANALYSE actor_case_assignment;
actor_case_assignment has its own primary key and a "role" field in addition
to the id
-> Index Scan using case_data_case_id on
case_data (cost=0.00..3.66 rows=1 width=39) (actual time=9.14..9.15 rows=1
loops=5882)
Index Cond: (case_data.case_id =
"outer".case_id)
Total runtime: 120038.60 msec
> You might have to resort to brute force, like "set enable_nestloop=false".
> Just out of curiosity, what do you get if you do that?
I get a different plan, but similar execution time:
Limit (cost=323437.13..323437.13 rows=1 width=115) (actual
time=170921.89..170922.95 rows=1000 loops=1)
> It looks like you are running with the default statistics target (10).
> Try boosting it to 100 or even more for this column (see ALTER TABLE
> SET STATISTICS, then re-ANALYZE) and see if the estimate gets better.
Here are the results & a few more clues:
prod1=# alter table actor alter column
> >actor_full_name_uppercase on actor (cost=0.00..6.01 rows=1 width=42)
> ^^
> >(actual time=37.62..677.44 rows=3501 loops=1)
> ^
> Nick, can you find out why this row count estimation is so far off?
^^^
>(actual time=37.62..677.44 rows=3501 loops=1)
^
> Nick, can you find out why this row count estimation is so far off?
It's actually correct:
prod1=# select count(actor_id) from actor where actor_full_name_uppercase
like 'SANDERS%';
count
---
3501
(1
-> Index Scan using case_data_case_id on
case_data (cost=0.00..3.66 rows=1 width=39) (actual time=9.14..9.15 rows=1
loops=5882)
Index Cond: (case_data.case_id =
"outer".case_id)
Total runtime: 120038.60 msec
(17 rows)
Any ideas?
> Have you checked these pages? They've been posted on this list numerous
> times:
> http://techdocs.postgresql.org
> http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
> http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html
>
Josh- It would be great to have a link to
The performance list seemed to be off-line for a while, so I posed the same
question on the admin list and Tom Lane has been helping in that forum.
-Nick
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] Behalf Of Nick
> Fankhauser
> Sent: Monda
2) Or alternately, am I totally missing what n-distinct is supposed to
denote?
Thanks!
-Nick
-----
Nick Fankhauser
[EMAIL PROTECTED] Phone 1.765.965.7363 Fax 1.765.962.9788
doxpop - Court records at your fingertips - ht
> Yes I Analyze also, but there was no need to because it was a fresh brand
> new database.
This apparently wasn't the source of problem since he did an analyze anyway,
but my impression was that a fresh brand new database is exactly the
situation where an analyze is needed- ie: a batch of data h
> For small records
> it is not much problems. But as the records grew (up to 12,000
> records) the
> difference is quite significant.
Although there are many tuning options, I'd suggest starting by making sure
you have an index (unique in cases where appropriate) on accposd.date
accposd.item, i
Sebastien-
I have a similar nightly process to keep our development system synched with
production. I just do a complete pg_dump of production, do a dropdb &
createdb to empty the database for development, and then restore the whole
db from the pg_dump file. Our database is about 12 GB currently,
>Can someone tell me what effective_cache_size should be set to?
You may be able to intuit this from my last post, but if I understand
correctly, what you should be doing is estimating how much memory is likely
to be "left over" for the OS to do disk caching with after all of the basic
needs of
om, then 1/2 GB should be left available for the effective
cache size.
I've never been tempted to turn fsync off. That seems like a risky move.
Regards,
-Nick
-----
Nick Fankhauser
[EMAIL PROTECTED] Phone 1.765.965.7
for headroom, then 1/2 GB should be left available
for the effective cache size.
I've never been tempted to turn fsync off. That seems like a risky move.
Regards,
-Nick
-----
Nick Fankhauser
[EMAIL PROTECTED] Phone 1.765.965.
I'm confused:
Ang Chin Han wrote:
> We've been using ext3fs for our production systems. (Red Hat Advanced
> Server 2.1)
Vincent van Leeuwen wrote:
> I'd upgrade to a journaling filesystem as soon as possible for
> reliability.
...About one year ago I considered moving to a journaling file syst
Thanks for the suggestions in the FS types- especially the Debian oriented
info. I'll start by playing with the memory allocation parameters that I
originally listed (seems like they should provide results in a way that is
unaffected by the disk IO). Then once I have them at optimal values, move o
> Wrong, actually. Sort memory is allocated *per sort*, not per
connnection or
> per query. So a single complex query could easily use 4xsort_mem if it
has
> several merge joins ...
Thanks for the correction- it sounds like this is one where usage can't be
precisely controlled in a dynamic us
Shridhar-
I appreciate your thoughts- I'll be running some before & after tests on
this using one of our development/hot-swap boxes, so I'll report the results
back to the list.
A few more thoughts/questions:
> 1. 30 users does not seem to be much of a oevrhead. If possible
> try doing away with
> I still would like some guidance on tunning FreeBSD (shmmax and
> shmmaxpgs).
> Do I need to even touch these settings?
Stephen- I have no idea what these are set to by default in FreeBSD, but
here's the page that covers changing it in the postgresql docs:
http://www.postgresql.org/docs/7.3/st
size.
Any thoughts? Is this a sane plan? Are there other parameters I should
consider changing first?
Thanks!
-Nick
-
Nick Fankhauser
[EMAIL PROTECTED] Phone 1.765.965.736
30 matches
Mail list logo
