Re: Problems with adding a is not null to a query.

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Tim Uckun <timuckun(at)gmail(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Problems with adding a is not null to a query.
Date: 2011-01-15 15:45:03
Message-ID: 28545.1295106303@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Tim Uckun <timuckun(at)gmail(dot)com> writes:
> I reported this in the pgsql-general list and was instructed to send
> the analaze outputs here.

This isn't a bug, it's just a poor choice of plan based on a bad
statistical estimate. The planner is estimating that there are 2643
rows having domain_id = 157, when actually there are none whatsoever,
as can be seen here:

> " -> Bitmap Index Scan on
> index_topical_urls_on_domain_id_and_consolidated_url_id
> (cost=0.00..104.63 rows=2643 width=0) (actual time=44.629..44.629
> rows=0 loops=1)"
> " Index Cond: (domain_id = 157)"

Possibly the table's never been ANALYZEd ... do you have autovacuum
enabled? If it has been analyzed reasonably recently, then it might be
necessary to crank up the statistics target to get a better estimate.
It's difficult to give detailed advice when you haven't mentioned what
PG version you're running.

regards, tom lane

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Anthony Manfredi 2011-01-15 15:50:23 date_trunc check constraint causes errors when restoring in a db with a different time zone
Previous Message Tim Uckun 2011-01-15 13:14:32 Problems with adding a is not null to a query.