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)))
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
