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:
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 see
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)
--postgresql.conf:
shared_buffers = 114688 # min 16
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 the last
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
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 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,
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
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
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
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
11 matches
Mail list logo
