Re: Query planner question

On Mon, 25 Aug 2014 09:09:07 -0700
Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> On Wed, Aug 20, 2014 at 6:16 PM, Soni M <diptatapa(at)gmail(dot)com> wrote:
>
> > Hi Everyone,
> >
> > I have this query :
> >
> > select t.ticket_id ,
> > tb.transmission_id
> > from ticket t,
> > transmission_base tb
> > where t.latest_transmission_id = tb.transmission_id
> > and t.ticket_number = tb.ticket_number
> > and tb.parse_date > ('2014-07-31');
> >
> > Execution plan: http://explain.depesz.com/s/YAak
> >
> > Indexes on ticket :
> > "ticket_pkey" PRIMARY KEY, btree (ticket_id) CLUSTER
> > "ticket_by_latest_transmission" btree (latest_transmission_id)
> > "ticket_by_ticket_number" btree (ticket_number)
> >
> > This query only returns some portions of rows from ticket table.
> > The question is, Why does postgres need to get all the rows from ticket
> > table in order to complete this query?
> > Can't postgres use indexes to get only needed rows on ticket table?
> >
> > I try set seqscan to off, but still index scan try to get all rows on
> > ticket table.
> > Here's the execution plan : http://explain.depesz.com/s/abH2

That's probably not the best approach, it's likely that something is feeding
the planner wrong information. An EXPLAIN ANALYZE might reveal if that's the
case.

Some other things to check: are these two tables being analyzed frequently
enough that their statistics are up to date? (EXPLAIN ANALYZE will generally
show if that's a problem too). It would seem that the planner thinks that
the distribution of tb.ticket_number is large enough that it will probably
have to fetch most of the rows from ticket anyway, which is a logical reason
for it to skip the index and just do a seq scan. Can you confirm/deny whether
that's the case? If not, and you're analyzing the tables often enough, you
may need to raise your statistics target on those tables.

--
Bill Moran
I need your help to succeed:
http://gamesbybill.com