Re: Query planner question

Soni M wrote
> On Fri, Aug 22, 2014 at 9:10 PM, Alban Hertroys <

> haramrae@

> > wrote:
>
>> On 22 August 2014 14:26, Soni M <

> diptatapa@

> > 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)

Can you provide EXPLAIN ANALYZE for all three queries?

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Query-planner-question-tp5815659p5815981.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.