I responded yesterday, but it seems to have gotten caught up because it was too big with the attachments... Here it is again.
Sorry about not posting correctly, hopefully I did it right this time.
So I wanted to see if Sql Server (2014) could handle this type of query differently than Postgres (9.5.1), so I got an instance of express installed and ported the test script to it.
I updated my Postgres script so the data is the same in each server. The end result is Sql Server seems to be able to optimize all of these queries MUCH better than Postgres.
I disabled parallelism in Sql Server to make the comparison fair.
I've attached the explain analyze results for Postgres, and the execution plan for Sql Server (in picture form... don't know a better way)
Results are:
--Sql Server:15ms average
--Postgres: 6ms average
SELECT *
FROM header
INNER JOIN header_total
ON header.header_id = header_total.header_id
WHERE header.header_id = 26;
--Sql Server: 15ms average
--Postgres: 1250ms average
SELECT *
FROM header
INNER JOIN header_total
ON header.header_id = header_total.header_id
WHERE header.header_id < 27
AND header.header_id > 24;
--Sql Server: 567ms average
--Postgres: 1265ms average
SELECT *
FROM header
INNER JOIN header_total
ON header.header_id = header_total.header_id
WHERE header.description like '%5%';
--Sql Server: 15ms average
--Postgres: 1252ms average
SELECT *
FROM header_total
WHERE header_total.header_id IN (
SELECT header_id
FROM header
WHERE header.header_id < 27
AND header.header_id > 24);
Here are the sql server execution plans as links rather than attachments:
