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.
Here is the explain plan on explain.depesz.com
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!
(lead_id, name, upper(value_string::text))
? And do you really need 5000 results?lead_detail
- have you run an(auto)analyze
on that table recently? And which version of PostgreSQL are you using?