Hi,
I have a problem with the below query, when i do explain on the
below query on my live database it doesnt use any index specified on the
tables and does seq scan on the table which is 400k records. But if i
copy the same table onto a different database on a different machine it
uses all the indexes specified and query runs much quicker. I ran
analyze, vacuum analyze and rebuilt indexes on the live database but
still there is no difference in the performance. Can anyone tell why
this odd behavior ?
Thanks!
Query
--------
SELECT a.total as fsbos, b.total as foreclosures, c.total as
auctions, d.latestDate as lastUpdated
FROM ((SELECT count(1) as total
FROM Properties p INNER JOIN Datasources ds
ON p.datasource = ds.sourceId
WHERE p.countyState = 'GA'
AND ds.sourceType = 'fsbo'
AND p.status in (1,2)
)) a,
((SELECT count(1) as total
FROM Properties p INNER JOIN Datasources ds
ON p.datasource = ds.sourceId
WHERE p.countyState = 'GA'
AND ds.sourceType = 'foreclosure'
AND (p.status in (1,2)
OR (p.status = 0 AND p.LastReviewed2 >= current_timestamp -
INTERVAL '14 days') )
)) b,
((SELECT count(1) as total
FROM Properties p
WHERE p.datasource = 1087
AND p.countyState = 'GA'
AND p.status in (1,2)
)) c,
((SELECT to_char(max(entryDate2), 'MM/DD/YYYY HH24:MI') as latestDate
FROM Properties p
WHERE p.countyState = 'GA'
)) d
Explain from the Live database
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=1334730.95..1334731.02 rows=1 width=56)
-> Nested Loop (cost=1026932.25..1026932.30 rows=1 width=24)
-> Nested Loop (cost=704352.11..704352.14 rows=1 width=16)
-> Subquery Scan b (cost=375019.89..375019.90 rows=1
width=8)
-> Aggregate (cost=375019.89..375019.89 rows=1
width=0)
-> Hash Join (cost=308.72..374844.49
rows=70158 width=0)
Hash Cond: ("outer".datasource =
"inner".sourceid)
-> Seq Scan on properties p
(cost=0.00..373289.10 rows=72678 width=4)
Filter: ((countystate =
'GA'::bpchar) AND ((status = 0) OR (status = 1) OR (status = 2)) AND
((lastreviewed2 >= (('now'::text)::timestamp(6) with time zone - '14
days'::interval)) OR (status = 1) OR (status = 2)))
-> Hash (cost=288.05..288.05
rows=8267 width=4)
-> Seq Scan on datasources ds
(cost=0.00..288.05 rows=8267 width=4)
Filter: ((sourcetype)::text
= 'foreclosure'::text)
-> Subquery Scan c (cost=329332.22..329332.23 rows=1
width=8)
-> Aggregate (cost=329332.22..329332.22 rows=1
width=0)
-> Seq Scan on properties p
(cost=0.00..329321.06 rows=4464 width=0)
Filter: ((datasource = 1087) AND
(countystate = 'GA'::bpchar) AND ((status = 1) OR (status = 2)))
-> Subquery Scan a (cost=322580.14..322580.15 rows=1 width=8)
-> Aggregate (cost=322580.14..322580.14 rows=1 width=0)
-> Hash Join (cost=288.24..322579.28 rows=344
width=0)
Hash Cond: ("outer".datasource =
"inner".sourceid)
-> Seq Scan on properties p
(cost=0.00..321993.05 rows=39273 width=4)
Filter: ((countystate = 'GA'::bpchar)
AND ((status = 1) OR (status = 2)))
-> Hash (cost=288.05..288.05 rows=75 width=4)
-> Seq Scan on datasources ds
(cost=0.00..288.05 rows=75 width=4)
Filter: ((sourcetype)::text =
'fsbo'::text)
-> Subquery Scan d (cost=307798.70..307798.72 rows=1 width=32)
-> Aggregate (cost=307798.70..307798.71 rows=1 width=8)
-> Seq Scan on properties p (cost=0.00..307337.04
rows=184666 width=8)
Filter: (countystate = 'GA'::bpchar)
Explain on the Copy of the Live database for the same query
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=5380.81..5380.88 rows=1 width=56)
-> Nested Loop (cost=3714.30..3714.35 rows=1 width=48)
-> Nested Loop (cost=2687.15..2687.18 rows=1 width=40)
-> Subquery Scan a (cost=1022.76..1022.77 rows=1 width=8)
-> Aggregate (cost=1022.76..1022.76 rows=1 width=0)
-> Nested Loop (cost=0.00..1022.75 rows=2
width=0)
-> Seq Scan on datasources ds
(cost=0.00..4.44 rows=2 width=4)
Filter: ((sourcetype)::text =
'fsbo'::text)
-> Index Scan using
idx_properties_datasourcestateauctiondate on properties p
(cost=0.00..509.14 rows=2 width=4)
Index Cond: (p.datasource =
"outer".sourceid)
Filter: ((countystate =
'GA'::bpchar) AND ((status = 1) OR (status = 2)))
-> Subquery Scan d (cost=1664.39..1664.40 rows=1 width=32)
-> Aggregate (cost=1664.39..1664.39 rows=1 width=8)
-> Index Scan using properties_idx_search on
properties p (cost=0.00..1663.35 rows=416 width=8)
Index Cond: (countystate = 'GA'::bpchar)
-> Subquery Scan b (cost=1027.15..1027.16 rows=1 width=8)
-> Aggregate (cost=1027.15..1027.15 rows=1 width=0)
-> Nested Loop (cost=0.00..1027.14 rows=3 width=0)
-> Seq Scan on datasources ds
(cost=0.00..4.44 rows=2 width=4)
Filter: ((sourcetype)::text =
'foreclosure'::text)
-> Index Scan using
idx_properties_datasourcestateauctiondate on properties p
(cost=0.00..511.32 rows=3 width=4)
Index Cond: (p.datasource =
"outer".sourceid)
Filter: ((countystate = 'GA'::bpchar)
AND ((status = 0) OR (status = 1) OR (status = 2)) AND ((lastreviewed2
>= (('now'::text)::timestamp(6) with time zone - '14 days'::interval))
OR (status = 1) OR (status = 2)))
-> Subquery Scan c (cost=1666.51..1666.52 rows=1 width=8)
-> Aggregate (cost=1666.51..1666.51 rows=1 width=0)
-> Index Scan using properties_idx_search on properties
p (cost=0.00..1666.46 rows=18 width=0)
Index Cond: (countystate = 'GA'::bpchar)
Filter: ((datasource = 1087) AND ((status = 1) OR
(status = 2)))