[postgis-devel] Re: [postgis-users] Possible index stats problem?

Mark Cave-Ayland m.cave-ayland at webbased.co.uk
Tue Sep 14 05:18:29 PDT 2004


> -----Original Message-----
> From: strk at refractions.net [mailto:strk at refractions.net] 
> Sent: 14 September 2004 12:48
> To: Mark Cave-Ayland
> Cc: postgis-devel at postgis.refractions.net; aflorio at gisplan.com.br
> Subject: Re: [postgis-devel] Re: [postgis-users] Possible 
> index stats problem?
> 
> 
> Mark. Note that Alexander estimates are better with the 
> default statistics target. The problem is not the selectivity 
> estimate, but the cost estimate itself. I've tried on my own data:
> 
> Seq Scan on plmshp02_1
> (cost=0.00..19.14 rows=26 width=81)
> (actual time=0.54..61.68 rows=75 loops=1)
> Total runtime: 61.83 msec
>  
> Index Scan using plmshp02_1_gist on plmshp02_1 
> (cost=0.00..81.53 rows=26 width=81) (actual time=0.39..19.22 
> rows=75 loop=1) Total runtime: 19.37 msecs
> 
> Compare the 'cost' estimate and actual runtime. 
> cost=0.00..19.14 => runtime 61.83 ms cost=0.00..81.53 => 
> runtime 19.37 ms
> 
> It runs faster when cost estimate is higher !!
> Alexander's report also show this.
> 
> --strk;


Hi strk,

Sorry I missed the original point of your email :) To me, this indicates
that the settings in the "Planner Cost Constants" section of
postgresql.conf are mismatched to the hardware (as you probably know,
postgresql.conf is very conservative by default). I would try setting
random_page_cost lower (maybe between 1 and 2) and restarting the
postmaster to see if that gives you a better correlation.


Kind regards,

Mark.

---

Mark Cave-Ayland
Webbased Ltd.
Tamar Science Park
Derriford
Plymouth
PL6 8BX
England

Tel: +44 (0)1752 764445
Fax: +44 (0)1752 764446


This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender. You
should not copy it or use it for any purpose nor disclose or distribute
its contents to any other person.





More information about the postgis-devel mailing list