I'm building a search interface for a web site that currently searches through approximately 2 million pieces of real estate with approximately 60 million attributes (address, city, state, ZIP, beds, baths, assessed value, etc).
The tables are laid out so that there is a LEAD table (2 million rows) with one entry for each piece of real estate and a corresponding LEAD_DETAIL table (60 million rows) that holds name/value pairs for the attributes.
The LEAD table has the following structure:
LEAD_ID BIGINT PRIMARY KEY LEAD_TYPE_NAME VARCHAR(50) CREATED_DATE_TIME TIMESTAMP MODIFIED_DATE_TIME TIMESTAMP RECEIVED_EMAIL_ID BIGINT NEW_LEAD SMALLINT FDOLD_ID INTEGER
And the LEAD_DETAIL table has the following structure:
LEAD_DETAIL_ID BIGINT PRIMARY KEY LEAD_ID BIGINT NAME VARCHAR(100) VALUE_STRING VARCHAR(1000) VALUE_NUMERIC NUMERIC VALUE_DATE DATE
The LEAD table has the following indexes (I've tried lots of indexing strategies to try and get this to work):
"lead_pkey" PRIMARY KEY, btree (lead_id) "lead_created_date_time_idx" btree (created_date_time) "lead_created_date_time_idx1" btree (created_date_time DESC) "lead_created_date_time_lead_id_idx" btree (created_date_time DESC, lead_id) "lead_fdold_id_idx" btree (fdold_id) "lead_lead_id_created_date_time_idx" btree (lead_id, created_date_time DESC) "lead_lead_id_lead_type_name_created_date_time_idx" btree (lead_id, lead_type_name, created_date_time DESC) "lead_modified_date_time_idx" btree (modified_date_time)
And the LEAD_DETAIL table has the following indexes:
"lead_detail_pkey" PRIMARY KEY, btree (lead_detail_id) "lead_detail_lead_id_name_key" UNIQUE CONSTRAINT, btree (lead_id, name) "lead_detail_name_value_date_idx" btree (name, value_date) "lead_detail_name_value_numeric_idx" btree (name, value_numeric) "lead_detail_name_value_string_idx" btree (name, value_string) "lead_detail_upper_value_string_idx" btree (name, upper(value_string::text))
Here is the query that I'm generating:
SELECT lead.lead_id FROM lead, lead_detail WHERE lead_detail.name = 'State' AND UPPER(lead_detail.value_string) = 'FL' AND lead.lead_id = lead_detail.lead_id ORDER BY lead.created_date_time DESC LIMIT 5000
Now, the distribution of the data is such that currently every lead is in the state of FL, so this is not exactly a highly "selective" query. More selective queries, e.g. for a small county, are quite fast.
My question is, given that we are not preventing users from issuing non-selective queries, how can we quickly return the top-n results for large results? I was hoping to use an INDEX on the CREATED_DATE_TIME column in conjunction with an ORDER BY and a LIMIT to produce the results quickly.
I've been working on this for hours and could really use some help. Thanks in advance!