On Fri, Aug 22, 2014 at 9:10 PM, Alban Hertroys <haramrae@xxxxxxxxx> wrote:
On 22 August 2014 14:26, Soni M <diptatapa@xxxxxxxxx> wrote:Do you have an index on ticket (latest_transmission_id)?
> 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)
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,
