Re: Query planner question - Mailing list pgsql-general

On 22 August 2014 14:26, Soni M <diptatapa@gmail.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)?

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