Il 19/08/11 22:15, Scott Marlowe ha scritto:
On Fri, Aug 19, 2011 at 1:05 PM, Edoardo Panfili<edoardo@xxxxxxxx> wrote:[1] Plan for the firts query ------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=20.45..20.46 rows=1 width=931) (actual time=4457.775..4457.786 rows=76 loops=1) Sort Key: cartellino.id Sort Method: quicksort Memory: 74kB -> Hash Join (cost=8.32..20.44 rows=1 width=931) (actual time=243.679..4457.658 rows=76 loops=1) Hash Cond: (cartellino.idspecie = principale.id) -> Nested Loop (cost=0.00..9.81 rows=614 width=886) (actual time=4.094..4439.024 rows=18370 loops=1)The row estimate here is off by a factor of 30 or so. In this case a different join method would likely work better. It might be that cranking up stats for the columns involved will help, but if that doesn't change the estimates then we might need to look elsewhere. What's your work_mem and random_page_cost?
work_mem = 1MB random_page_cost = 4 I am using an SSD but the production system uses a standard hard disk. I did a try also with set default_statistics_target=10000; vacuum analyze cartellino; vacuum analyze specie; -- the base table for specienomi vacuum analyze confini_regioni; but is always 4617.023 ms Edoardo -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
