Re: Query planner question

[Jeff Janes <jeff.janes@xxxxxxxxx>]

On Wed, Aug 20, 2014 at 6:16 PM, Soni M <diptatapa@xxxxxxxxx> 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

If you want to force a nested loop, you probably need to disable the mergejoin as well, and maybe the hashjoin.  Forcing the planner to do things the way you want can be difficult.

Cheers,

Jeff