databasename=# explain select DISTINCT (case when resubmitted > created then
resubmitted else created end),a.app_id, a.appcode, a.firstname,
a.middlename, a.lastname, a.state, a.degree1, a.d1date, a.degree2, a.d2date,
a.salary, a.skill1, a.skill2, a.skill3, a.objective, a.employer, a.sic1,
a.sic2, a.sic3, a.prefs1, a.prefs2, a.sells from applicants as
a,applicants_resumes as ar,resumes_fti as rf where (a.created::date >
'01-06-2000' or a.resubmitted::date > '01-06-2000') and (rf.string
~'^test' ) and ar.app_id=a.app_id and rf.id=ar.oid order by (case when
resubmitted > created then resubmitted else created end) desc limit 10
offset 0;
NOTICE: QUERY PLAN:
Unique (cost=3981.58..4396.74 rows=722 width=220)
-> Sort (cost=3981.58..3981.58 rows=7220 width=220)
-> Hash Join (cost=1751.00..3518.84 rows=7220 width=220)
-> Hash Join (cost=1665.98..2634.96 rows=6132 width=216)
-> Seq Scan on applicants a (cost=0.00..585.74
rows=6132 width=208)
-> Hash (cost=1638.38..1638.38 rows=11038 width=8)
-> Seq Scan on applicants_resumes ar
(cost=0.00..1638.38 rows=11038 width=8)
-> Hash (cost=4.97..4.97 rows=32024 width=4)
-> Index Scan using resumes_fti_index on resumes_fti rf
(cost=0.00..4.97 rows=32024 width=4)
EXPLAIN
Both the app_id and the OID columns are indexed and I have done a VACUUM
ANALYZE in the applicants_resumes table yet I still get a seq scan on
them.... I'm trying to understand how this works so I can index the right
columns -- could someone please school me? :-)
When I enable likeplanning (in contrib) I get a totally differen plan but
over all it's slower.
Thanks!
-Mitch
