need to repeat the same condition on joined tables in order to choose the proper plan

Hi performance gurus,

One of the reasons I prefer PostgreSQL is because it does not implement hints. However I have a situation which seems like I am forced to use a hint-like statement:
 
I have 2 tables in PostgreSQL 9.0:

tcpsessions - about 4 Mrows in lab, hundreds of Mrows in production
primary key(detectorid, id)

tcpsessiondata - about 2-5 times bigger than tcpsessions
Foreign key(detectorid, sessionid) References tcpsessions(detectorid,id)
There is an index on (detectorid, sessionid)

For completeness tcpsessiondata is actually partitioned according to the official documentation but I will save you the details if that is not necessary. For the purpose of this message, all the data will be available in one child table: tcpsessiondata_default

When I run the following simple query:

select
    (createdtime / 60000000000) as timegroup,
    (sum(datafromsource)+sum(datafromdestination)) as numbytes,
    (sum(packetsfromsource)+sum(packetsfromdestination)) as numpackets
from
    tcpsessiondata SD, tcpsessions SS
where
    SD.detectorid = SS.detectorid
    and SD.sessionid = SS.id
    and SD.detectorid = 1
    and SD.sessionid >= 1001000000000::INT8 and SD.sessionid <= 2001000000000::INT8
group by
    timegroup
order by
    timegroup asc

I get the following plan:
"Sort  (cost=259126.13..259126.63 rows=200 width=32) (actual time=32526.762..32526.781 rows=20 loops=1)"
"  Output: ((sd.createdtime / 60000000000::bigint)), (((sum(sd.datafromsource) + sum(sd.datafromdestination)) / 1048576::numeric)), ((sum(sd.packetsfromsource) + sum(sd.packetsfromdestination)))"
"  Sort Key: ((sd.createdtime / 60000000000::bigint))"
"  Sort Method:  quicksort  Memory: 26kB"
"  ->  HashAggregate  (cost=259112.49..259118.49 rows=200 width=32) (actual time=32526.657..32526.700 rows=20 loops=1)"
"        Output: ((sd.createdtime / 60000000000::bigint)), ((sum(sd.datafromsource) + sum(sd.datafromdestination)) / 1048576::numeric), (sum(sd.packetsfromsource) + sum(sd.packetsfromdestination))"
"        ->  Hash Join  (cost=126553.43..252603.29 rows=520736 width=32) (actual time=22400.430..31291.838 rows=570100 loops=1)"
"              Output: sd.createdtime, sd.datafromsource, sd.datafromdestination, sd.packetsfromsource, sd.packetsfromdestination, (sd.createdtime / 60000000000::bigint)"
"              Hash Cond: (sd.sessionid = ss.id)"
"              ->  Append  (cost=0.00..100246.89 rows=520736 width=42) (actual time=2382.160..6226.906 rows=570100 loops=1)"
"                    ->  Seq Scan on appqosdata.tcpsessiondata sd  (cost=0.00..18.65 rows=1 width=42) (actual time=0.002..0.002 rows=0 loops=1)"
"                          Output: sd.createdtime, sd.datafromsource, sd.datafromdestination, sd.packetsfromsource, sd.packetsfromdestination, sd.detectorid, sd.sessionid"
"                          Filter: ((sd.sessionid >= 1001000000000::bigint) AND (sd.sessionid <= 2001000000000::bigint) AND (sd.detectorid = 1))"
"                    ->  Bitmap Heap Scan on appqosdata.tcpsessiondata_default sd  (cost=11001.37..100228.24 rows=520735 width=42) (actual time=2382.154..5278.319 rows=570100 loops=1)"
"                          Output: sd.createdtime, sd.datafromsource, sd.datafromdestination, sd.packetsfromsource, sd.packetsfromdestination, sd.detectorid, sd.sessionid"
"                          Recheck Cond: ((sd.detectorid = 1) AND (sd.sessionid >= 1001000000000::bigint) AND (sd.sessionid <= 2001000000000::bigint))"
"                          ->  Bitmap Index Scan on idx_tcpsessiondata_default_detectoridandsessionid  (cost=0.00..10871.19 rows=520735 width=0) (actual time=2351.865..2351.865 rows=574663 loops=1)"
"                                Index Cond: ((sd.detectorid = 1) AND (sd.sessionid >= 1001000000000::bigint) AND (sd.sessionid <= 2001000000000::bigint))"
"              ->  Hash  (cost=72340.48..72340.48 rows=3628356 width=10) (actual time=19878.891..19878.891 rows=3632586 loops=1)"
"                    Output: ss.detectorid, ss.id"
"                    Buckets: 8192  Batches: 64  Memory Usage: 2687kB"
"                    ->  Seq Scan on appqosdata.tcpsessions ss  (cost=0.00..72340.48 rows=3628356 width=10) (actual time=627.164..14586.202 rows=3632586 loops=1)"
"                          Output: ss.detectorid, ss.id"
"                          Filter: (ss.detectorid = 1)"
"Total runtime: 32543.224 ms"


As we can see the planner decides to go for an index scan on tcpsessiondata_default (as expected) and for a seq scan on tcpsessions. However if I add the following ugly condition to my query:
    and SS.detectorid = 1
    and SS.id >= 1001000000000::INT8 and SS.id <= 2001000000000::INT8
so that the full query now becomes:

select
    (createdtime / 60000000000) as timegroup,
    (sum(datafromsource)+sum(datafromdestination)) as numbytes,
    (sum(packetsfromsource)+sum(packetsfromdestination)) as numpackets
from
    tcpsessiondata SD, tcpsessions SS
where
    SD.detectorid = SS.detectorid
    and SD.sessionid = SS.id
    and SD.detectorid = 1
    and SD.sessionid >= 1001000000000::INT8 and SD.sessionid <= 2001000000000::INT8
    and SS.detectorid = 1
    and SS.id >= 1001000000000::INT8 and SS.id <= 2001000000000::INT8
group by
    timegroup
order by
    timegroup asc

well, now I have an index scan on tcpsessions as well and running time is 3 times less than the previous one:

"Sort  (cost=157312.59..157313.09 rows=200 width=32) (actual time=9682.748..9682.764 rows=20 loops=1)"
"  Output: ((sd.createdtime / 60000000000::bigint)), (((sum(sd.datafromsource) + sum(sd.datafromdestination)) / 1048576::numeric)), ((sum(sd.packetsfromsource) + sum(sd.packetsfromdestination)))"
"  Sort Key: ((sd.createdtime / 60000000000::bigint))"
"  Sort Method:  quicksort  Memory: 26kB"
"  ->  HashAggregate  (cost=157298.94..157304.94 rows=200 width=32) (actual time=9682.649..9682.692 rows=20 loops=1)"
"        Output: ((sd.createdtime / 60000000000::bigint)), ((sum(sd.datafromsource) + sum(sd.datafromdestination)) / 1048576::numeric), (sum(sd.packetsfromsource) + sum(sd.packetsfromdestination))"
"        ->  Hash Join  (cost=32934.67..150744.28 rows=524373 width=32) (actual time=3695.016..8370.629 rows=570100 loops=1)"
"              Output: sd.createdtime, sd.datafromsource, sd.datafromdestination, sd.packetsfromsource, sd.packetsfromdestination, (sd.createdtime / 60000000000::bigint)"
"              Hash Cond: (sd.sessionid = ss.id)"
"              ->  Append  (cost=0.00..100948.71 rows=524373 width=42) (actual time=2318.568..4799.985 rows=570100 loops=1)"
"                    ->  Seq Scan on appqosdata.tcpsessiondata sd  (cost=0.00..18.65 rows=1 width=42) (actual time=0.001..0.001 rows=0 loops=1)"
"                          Output: sd.createdtime, sd.datafromsource, sd.datafromdestination, sd.packetsfromsource, sd.packetsfromdestination, sd.detectorid, sd.sessionid"
"                          Filter: ((sd.sessionid >= 1001000000000::bigint) AND (sd.sessionid <= 2001000000000::bigint) AND (sd.detectorid = 1))"
"                    ->  Bitmap Heap Scan on appqosdata.tcpsessiondata_default sd  (cost=11080.05..100930.06 rows=524372 width=42) (actual time=2318.563..3789.844 rows=570100 loops=1)"
"                          Output: sd.createdtime, sd.datafromsource, sd.datafromdestination, sd.packetsfromsource, sd.packetsfromdestination, sd.detectorid, sd.sessionid"
"                          Recheck Cond: ((sd.detectorid = 1) AND (sd.sessionid >= 1001000000000::bigint) AND (sd.sessionid <= 2001000000000::bigint))"
"                          ->  Bitmap Index Scan on idx_tcpsessiondata_default_detectoridandsessionid  (cost=0.00..10948.96 rows=524372 width=0) (actual time=2305.322..2305.322 rows=574663 loops=1)"
"                                Index Cond: ((sd.detectorid = 1) AND (sd.sessionid >= 1001000000000::bigint) AND (sd.sessionid <= 2001000000000::bigint))"
"              ->  Hash  (cost=30159.60..30159.60 rows=185726 width=10) (actual time=1345.307..1345.307 rows=194971 loops=1)"
"                    Output: ss.detectorid, ss.id"
"                    Buckets: 8192  Batches: 4  Memory Usage: 2297kB"
"                    ->  Bitmap Heap Scan on appqosdata.tcpsessions ss  (cost=3407.46..30159.60 rows=185726 width=10) (actual time=483.572..1069.292 rows=194971 loops=1)"
"                          Output: ss.detectorid, ss.id"
"                          Recheck Cond: ((ss.id >= 1001000000000::bigint) AND (ss.id <= 2001000000000::bigint))"
"                          Filter: (ss.detectorid = 1)"
"                          ->  Bitmap Index Scan on idx_tcpsessions_id  (cost=0.00..3361.02 rows=201751 width=0) (actual time=451.242..451.242 rows=219103 loops=1)"
"                                Index Cond: ((ss.id >= 1001000000000::bigint) AND (ss.id <= 2001000000000::bigint))"
"Total runtime: 9682.905 ms"


Let me also add that if I remove the conditions on SD but keep the conditions on SS, then I get an index scan on tcpsessions BUT a seq scan on tcpsessiondata.

Let's now suppose that the index scan on both tables is the best choice as the planner itself selects it in one of the 3 cases. (It is also the faster plan as we extract only 200 000 rows out of 4 millions in this example). But I am really surprised to see that the planner needs me to explicitly specify the same condition twice like this:

    SD.detectorid = SS.detectorid
    and SD.sessionid = SS.id
    and SD.detectorid = 1
    and SD.sessionid >= 1001000000000::INT8 and SD.sessionid <= 2001000000000::INT8
    and SS.detectorid = 1
    and SS.id >= 1001000000000::INT8 and SS.id <= 2001000000000::INT8

in order to use the primary key on SS, even if it is absolutely clear that "SD.detectorid = SS.detectorid and SD.sessionid = SS.id". Well I hope you agree that repeating the same condition on SS seems very like giving a hint to use the index there. But I feel very uncomfortable to use such an ugly condition, especially knowing that I am doing it to "force an index". On the other hand I am terrified that we may go in production for a seq scan on hundreds of millions of rows just to extract 200 000.

Would you please explain that behavior and how would you suggest to proceed?

Thanks for any comments,
Svetlin Manavski