Re: enable_sort optimization problem

Dave E Martin <postgresql-to(dot)dave(at)dave(dot)to> writes:
> I have noticed that if I set enable_sort=false in the .conf file, my
> queries are running faster.

You mean one specific example is running faster. If you do that you'll
probably slow down other queries.

It looks like the main estimation error is here:

> -> Nested Loop (cost=0.00..1012208.81 rows=159
> width=76) (actual time=126.277..254.709 rows=31 loops=1)
> -> Index Scan Backward using pk_outageid on
> outages (cost=0.00..252480.62 rows=132245 width=52) (actual
> time=77.021..77.484 rows=31 loops=1)
> -> Index Scan using
> ipinterface_nodeid_ipaddr_ismanaged_idx on ipinterface (cost=0.00..5.73
> rows=1 width=40) (actual time=5.304..5.686 rows=1 loops=31)
> Index Cond: (("outer".nodeid =
> ipinterface.nodeid) AND (("outer".ipaddr)::text =
> (ipinterface.ipaddr)::text))

Why does it think that only 159 of the 132245 rows in outages will have
join partners in ipinterface? The actual results look like they all do.
It might be worth looking at the pg_stats rows for the join columns to
see if there's something odd about the statistics.

regards, tom lane