Re: Query planner question

On Fri, Aug 22, 2014 at 9:10 PM, Alban Hertroys <haramrae(at)gmail(dot)com> wrote:

> On 22 August 2014 14:26, Soni M <diptatapa(at)gmail(dot)com> wrote:
> > Currently we have only latest_transmission_id as FK, described here :
> > TABLE "ticket" CONSTRAINT "fkcbe86b0c6ddac9e" FOREIGN KEY
> > (latest_transmission_id) REFERENCES transmission_base(transmission_id)
> >
> > Change the query to include only FK still result the same:
> > explain select t.ticket_id ,
> > tb.transmission_id
> > from ticket t,
> > transmission_base tb
> > where t.latest_transmission_id = tb.transmission_id
> > and tb.parse_date > ('2014-07-31');
> > QUERY PLAN
> >
> ----------------------------------------------------------------------------------------------------------------------------------
> > Hash Join (cost=113928.06..2583606.96 rows=200338 width=8)
> > Hash Cond: (t.latest_transmission_id = tb.transmission_id)
> > -> Seq Scan on ticket t (cost=0.00..1767767.26 rows=69990826
> width=8)
> > -> Hash (cost=108923.38..108923.38 rows=400374 width=4)
> > -> Index Scan using transmission_base_by_parse_date on
> > transmission_base tb (cost=0.00..108923.38 rows=400374 width=4)
> > Index Cond: (parse_date > '2014-07-31 00:00:00'::timestamp
> > without time zone)
> > (6 rows)
>
> Do you have an index on ticket (latest_transmission_id)?
>
> Yes, both t.latest_transmission_id and tb.transmission_id is indexed.

Indexes:
"transmission_base_pkey" PRIMARY KEY, btree (transmission_id) CLUSTER
Indexes:
"ticket_by_latest_transmission" btree (latest_transmission_id)

> --
> If you can't see the forest for the trees,
> Cut the trees and you'll see there is no forest.
>

--
Regards,

Soni Maula Harriz