Skip site navigation (1) Skip section navigation (2)

Re: two queries and dual cpu (perplexed)

From: "Shoaib Burq (VPAC)" <sab(at)vpac(dot)org>
To: John A Meinel <john(at)arbash-meinel(dot)com>
Cc: Russell Smith <mr-russ(at)pws(dot)com(dot)au>,Jeff <threshar(at)torgo(dot)978(dot)org>, <pgsql-performance(at)postgresql(dot)org>
Subject: Re: two queries and dual cpu (perplexed)
Date: 2005-04-22 03:33:31
Message-ID: Pine.LNX.4.44.0504221328410.14851-200000@hp.vpac.org (view raw or flat)
Thread:
Lists: pgsql-performance
Please see attached the output from explain analyse. This is with the 

	shared_buffers = 10600
	work_mem = 102400
	enable_seqscan = true

BTW I guess should mention that I am doing the select count(*) on a View.

Ran the Explain analyse with the nestedloop disabled but it was taking 
forever... and killed it after 30mins.

Thanks
shoaib
On Thu, 21 Apr 2005, John A Meinel wrote:

> Shoaib Burq (VPAC) wrote:
> 
> >Just tried it with the following changes:
> >
> >shared_buffers = 10600
> >work_mem = 102400
> >enable_seqscan = false
> >
> >still no improvement
> >
> >Ok here's the Plan with the enable_seqscan = false:
> >ausclimate=# explain ANALYZE  select count(*) from "getfutureausclimate";
> >
> >
> Actually, you probably don't want enable_seqscan=off, you should try:
> SET enable_nestloop TO off.
> The problem is that it is estimating there will only be 44 rows, but in
> reality there are 13M rows. It almost definitely should be doing a
> seqscan with a sort and merge join.
> 
> Also, please attach you explain analyzes, the wrapping is really hard to
> read.
> 
> I don't understand how postgres could get the number of rows that wrong.
> 
> It seems to be misestimating the number of entries in IX_ClimateId
> 
> Here:
> 
> ->  Index Scan using "PK_Aus40_DEM" on "Aus40_DEM"  (cost=0.00..6.01 rows=1 width=16) (actual time=0.005..0.006 rows=1 loops=13276368)
>     Index Cond: ("outer"."AusPosNumber" = "Aus40_DEM"."AusPosNumber")
> ->  Index Scan using "PK_CurrentAusClimate" on "CurrentAusClimate"  (cost=0.00..46.20 rows=11 width=14) (actual time=0.007..0.009 rows=1 loops=13276368)
> 
> The first index scan is costing you 0.006*13276368=79s, and the second one is 119s.
> 
> I can't figure out exactly what is where from the formatting, but the query that seems misestimated is:
> ->  Index Scan using "IX_ClimateId" on "ClimateChangeModel40"  (cost=0.00..1063711.75 rows=265528 width=20) (actual time=28.311..17212.703 rows=13276368 loops=1)
>     Index Cond: ("outer"."ClimateId" = "ClimateChangeModel40"."ClimateId")
> 
> Is there an unexpected correlaction between
> ClimateChangeModel40"."ClimateId" and whatever "outer" is at this point?
> 
> John
> =:->
> 
> 


Attachment: explain_analyse_getfutureausclimate.txt
Description: text/plain (3.8 KB)

In response to

Responses

pgsql-performance by date

Next:From: Alvaro HerreraDate: 2005-04-22 03:55:21
Subject: Re: foreign key performance
Previous:From: Enrico WeigeltDate: 2005-04-22 00:06:15
Subject: foreign key performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group