smiley2211 wrote:
>
> Hello all,
>
> I've made the changes to view to use UNION ALL and the where NOT IN
> suggestions...the query now takes a little under 3 hours instead of 5 --
> here is the EXPLAIN ANALYZE:
It seems you have disabled nested loops --- why? Try turning them back
on and let us
Hello all,
I've made the changes to view to use UNION ALL and the where NOT IN
suggestions...the query now takes a little under 3 hours instead of 5 --
here is the EXPLAIN ANALYZE:
*
smiley2211 wrote:
Here are the VIEWS in question: query = (explain analyze select id from
people_consent LIMIT 1;)
First thing I notice - you don't have any ordering, so the LIMIT isn't
returning a well-defined record. Might not matter in your particular
context.
CREATE OR REPLACE VIEW temp
smiley2211 wrote:
TOP shows CPU at 100% while executed the EXPLAIN ANALYZE...what does this
mean?
17519 postgres 25 0 3470m 43m 39m R 100 0.3 28:50.53 postmaster
It means it's busy. Probably sorting/eliminating duplicates (see my
answer posted just before this one).
Keep an eye on
TOP shows CPU at 100% while executed the EXPLAIN ANALYZE...what does this
mean?
17519 postgres 25 0 3470m 43m 39m R 100 0.3 28:50.53 postmaster
--
View this message in context:
http://www.nabble.com/Query-is-taking-5-HOURS-to-Complete-on-8.1-version-tf4019778.html#a11419885
Sent from t
On 7/3/07, smiley2211 <[EMAIL PROTECTED]> wrote:
CREATE OR REPLACE VIEW temp_consent2 AS
SELECT DISTINCT temp_consent.id, temp_consent.daterecorded
FROM temp_consent
WHERE temp_consent.answer::text = 'Yes'::text
ORDER BY temp_consent.daterecorded DESC, temp_consent.id;
Get rid of the
Here are the VIEWS in question: query = (explain analyze select id from
people_consent LIMIT 1;)
CREATE OR REPLACE VIEW temp_consent AS
SELECT p.id, max(en.enrolled_at) AS daterecorded, a.answer
FROM people p, enrollments en, encounters ec,
encounters_questions_answers eqa, questions_answers
smiley2211 wrote:
Here is the EXPLAIN after I changed some conf file - now I am running another
EXPLAIN ANALYZE which may take 5 or more hours to complete :,(
effective_cache = 17
Why has effective_cache changed from 80,000 to 170,000 - have you
stopped running some other application?
Here is the EXPLAIN after I changed some conf file - now I am running another
EXPLAIN ANALYZE which may take 5 or more hours to complete :,(
effective_cache = 17
enable_seqscan = on
enable _bitmapscan = on
QUERY PLAN
In response to smiley2211 <[EMAIL PROTECTED]>:
>
> This query is taking less than 5 minutes on 7.4 but over 5 hours on 8.1...
>
> PostgreSQL 8.1.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC)
> 4.1.0 (SUSE Linux)
> Total runtime: 20448310.101 ms = 5.6800862 hour
> (132 rows)
When was t
10 matches
Mail list logo
