i have table A and table B (with below data)
When i join as below , it takes 2seconds.
select * from A join B on person_name where person_name = 'John Doe';
However both below only take 100ms .
select * from A where person_name = 'John Doe';
select * from B where person_name = 'John Doe';
Can we optimize the join query to be completed in ~200ms .
Probably not. You have to understand what you are doing with the join query. If you have 10 records in A with "John Doe" and you have 20 records in B with "John Doe", your resultant join is 10 times 20 = 200. Why? Because each record in A will be matched with all 20 records in B, so 1st "John Doe" in A in joining with B will produce 20 results. So will the 2nd in A, 3rd in A and so on until the 10th in A. Hence 10 x 20 = 200.
Making some assumptions, a SELECT from A alone will return only 10 records. The other SELECT from B alone will return 20 records hence a total between the two queries of 30 records. 30 records vs 200 in the join - a 7 times difference.
If you only had 10 records in B with "John Doe", the join would still be 10 x 10 = 100 records. It is however, still 100 records vs 30 records.
Here's what may help. Make sure you have an index on person_name in BOTH tables A and B, not just one table. If you don't have both indexes, this can help speed up matters.
On the other hand, if your 10 "John Doe" records in A are really 10 separate people, then each of them has to join to the correct "John Doe" records in B (i.e. 1 to 1 ONLY) resulting in a total of 10 rows ONLY. In that case, you need to use something else to join (perhaps unique person identifier) that is available in both tables and that too must be indexed.
EXPLAIN (ANALYZE, BUFFERS)for the slow one, as formatted text.