I currently have an infrastructure that's based around SQL Server 2000.
I'm trying to move some of the data over to Postgres, partly to reduce
the load on the SQL Server machine and partly because some queries I'd
like to run are too slow to be usuable on SQL Server. Mostly likely over
time more and more data will move to Postgres. To help with this
transition, I created a Postgres plugin which queries the contents of
SQL Server tables via ODBC and returns a recordset. I then create views
around the function and I can then read from the SQL Server tables as if
they were local to Postgres.
I have four tables involved in this query. The major one is
provider_location, which has about 2 million rows and is stored in
Postgres. The other three are stored in SQL Server and accessed via
views like I mentioned above. They are network, network_state, and
xlat_tbl, and contain about 40, 250, and 500 rows. A simple select *
from any of the views takes somewhere around 50ms.
This query in question was written for SQL Server. I have no idea why it
was written in the form it was, but it ran at a reasonable speed when
all the tables were on one machine. Running the original query (after
adjusting for syntax differences) on Postgres resulted in a query that
would run for hours, continually allocating more RAM. I eventually had
to kill the process as it was devouring swap space. My assumption is
that Postgres is doing the ODBC query for each row of a join somewhere,
even though the function is marked stable (immutable didn't make a
difference).
Flattening the query made it run in a few minutes. I think the flattened
query is easier to read, and it runs faster, so I'm not complaining that
I can't use the original query. But I'd like to know exactly what causes
the bottleneck in the original query, and if there are other approaches
to solving the issue in case I need them in future queries.
Below is the original query, the explain output, the modified query, the
explain output, and the explain analyze output.
Ed
select
pl.network_id,n.name as
network_name,pl.state_cd,count(pl.state_cd) as provider_count
from development.provider_location pl,development.network n
where pl.network_id in (select ns.network_id
from development.network_state ns
where ns.from_date < current_time
and (ns.thru_date > current_time or
ns.thru_date is null)
and (ns.state_cd = pl.state_cd or ns.state_cd='')
)
and pl.network_id = n.network_id
and pl.state_cd is not null
and pl.state_cd in (select field_value from development.xlat_tbl
where field_name ='State_CD')
group by pl.state_cd,n.name,pl.network_id
order by pl.state_cd,network_name;
Explain:
GroupAggregate (cost=80548547.83..80549256.80 rows=47265 width=52)
-> Sort (cost=80548547.83..80548665.99 rows=47265 width=52)
Sort Key: pl.state_cd, odbc_select.name, pl.network_id
-> Hash Join (cost=30.01..80543806.14 rows=47265 width=52)
Hash Cond: (("outer".network_id)::text =
("inner".network_id)::text)
-> Hash IN Join (cost=15.01..80540931.61 rows=9453 width=20)
Hash Cond: (("outer".state_cd)::text =
("inner".field_value)::text)
-> Seq Scan on provider_location pl
(cost=0.00..80535150.29 rows=1890593 width=20)
Filter: ((state_cd IS NOT NULL) AND (subplan))
SubPlan
-> Function Scan on odbc_select
(cost=0.00..42.50 rows=2 width=32)
Filter: (((from_date)::text <
(('now'::text)::time(6) with time zone)::text) AND (((thru_date)::text >
(('now'::text)::time(6) with time zone)::text) OR (thru_date IS NULL))
AND (((state_cd)::text = ($0)::text) OR ((state_cd)::text = ''::text)))
-> Hash (cost=15.00..15.00 rows=5 width=32)
-> Function Scan on odbc_select
(cost=0.00..15.00 rows=5 width=32)
Filter: ((field_name)::text =
'State_CD'::text)
-> Hash (cost=12.50..12.50 rows=1000 width=64)
-> Function Scan on odbc_select (cost=0.00..12.50
rows=1000 width=64)
Flattened query:
select
pl.network_id,
n.name as network_name,
pl.state_cd,
count(pl.state_cd) as provider_count
from
development.network n,
development.network_state ns,
development.xlat_tbl xt,
development.provider_location pl
where
xt.field_name = 'State_CD'
and n.network_id = ns.network_id
and ns.from_date < current_timestamp
and (ns.thru_date > current_timestamp or ns.thru_date is null)
and (ns.state_cd = pl.state_cd or ns.state_cd='')
and pl.network_id = n.network_id
and pl.state_cd is not null
and pl.state_cd = xt.field_value
group by
pl.state_cd,
n.name,
pl.network_id
order by
pl.state_cd,
network_name;
Explain:
GroupAggregate (cost=190089.94..190129.90 rows=2664 width=52)
-> Sort (cost=190089.94..190096.60 rows=2664 width=52)
Sort Key: pl.state_cd, odbc_select.name, pl.network_id
-> Merge Join (cost=189895.73..189938.37 rows=2664 width=52)
Merge Cond: ("outer"."?column4?" = "inner"."?column3?")
-> Sort (cost=189833.40..189834.73 rows=533 width=52)
Sort Key: (pl.network_id)::text
-> Hash Join (cost=42.80..189809.26 rows=533 width=52)
Hash Cond: (("outer".network_id)::text =
("inner".network_id)::text)
Join Filter: ((("inner".state_cd)::text =
("outer".state_cd)::text) OR (("inner".state_cd)::text = ''::text))
-> Hash Join (cost=15.01..185908.10
rows=94530 width=20)
Hash Cond: (("outer".state_cd)::text =
("inner".field_value)::text)
-> Seq Scan on provider_location pl
(cost=0.00..166041.86 rows=3781186 width=20)
Filter: (state_cd IS NOT NULL)
-> Hash (cost=15.00..15.00 rows=5
width=32)
-> Function Scan on odbc_select
(cost=0.00..15.00 rows=5 width=32)
Filter: ((field_name)::text
= 'State_CD'::text)
-> Hash (cost=27.50..27.50 rows=113 width=64)
-> Function Scan on odbc_select
(cost=0.00..27.50 rows=113 width=64)
Filter: ((from_date <
('now'::text)::timestamp(6) with time zone) AND ((thru_date >
('now'::text)::timestamp(6) with time zone) OR (thru_date IS NULL)))
-> Sort (cost=62.33..64.83 rows=1000 width=64)
Sort Key: (odbc_select.network_id)::text
-> Function Scan on odbc_select (cost=0.00..12.50
rows=1000 width=64)
Explain Analyze:
"GroupAggregate (cost=190089.94..190129.90 rows=2664 width=52) (actual
time=254757.742..261725.786 rows=350 loops=1)"
" -> Sort (cost=190089.94..190096.60 rows=2664 width=52) (actual
time=254757.438..257267.224 rows=1316774 loops=1)"
" Sort Key: pl.state_cd, odbc_select.name, pl.network_id"
" -> Merge Join (cost=189895.73..189938.37 rows=2664 width=52)
(actual time=189325.877..203579.050 rows=1316774 loops=1)"
" Merge Cond: ("outer"."?column4?" = "inner"."?column3?")"
" -> Sort (cost=189833.40..189834.73 rows=533 width=52)
(actual time=189282.504..192284.766 rows=1316774 loops=1)"
" Sort Key: (pl.network_id)::text"
" -> Hash Join (cost=42.80..189809.26 rows=533
width=52) (actual time=1177.758..151180.472 rows=1316774 loops=1)"
" Hash Cond: (("outer".network_id)::text =
("inner".network_id)::text)"
" Join Filter: ((("inner".state_cd)::text =
("outer".state_cd)::text) OR (("inner".state_cd)::text = ''::text))"
" -> Hash Join (cost=15.01..185908.10
rows=94530 width=20) (actual time=1095.949..50495.766 rows=1890457 loops=1)"
" Hash Cond: (("outer".state_cd)::text =
("inner".field_value)::text)"
" -> Seq Scan on provider_location pl
(cost=0.00..166041.86 rows=3781186 width=20) (actual
time=1071.011..36224.961 rows=1891183 loops=1)"
" Filter: (state_cd IS NOT NULL)"
" -> Hash (cost=15.00..15.00 rows=5
width=32) (actual time=24.832..24.832 rows=0 loops=1)"
" -> Function Scan on odbc_select
(cost=0.00..15.00 rows=5 width=32) (actual time=24.469..24.724 rows=51
loops=1)"
" Filter: ((field_name)::text
= 'State_CD'::text)"
" -> Hash (cost=27.50..27.50 rows=113
width=64) (actual time=81.684..81.684 rows=0 loops=1)"
" -> Function Scan on odbc_select
(cost=0.00..27.50 rows=113 width=64) (actual time=75.288..81.200
rows=211 loops=1)"
" Filter: ((from_date <
('now'::text)::timestamp(6) with time zone) AND ((thru_date >
('now'::text)::timestamp(6) with time zone) OR (thru_date IS NULL)))"
" -> Sort (cost=62.33..64.83 rows=1000 width=64) (actual
time=43.301..1258.901 rows=1289952 loops=1)"
" Sort Key: (odbc_select.network_id)::text"
" -> Function Scan on odbc_select (cost=0.00..12.50
rows=1000 width=64) (actual time=43.010..43.109 rows=34 loops=1)"
"Total runtime: 261902.966 ms"
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
