speed up the query which is taking 2 seconds on 1000000 on active records also on PostgreSQL Sql

I’m creating a dynamic query on the server side beside the parameter. However, my query is taking 2 seconds to fetch the records. I’m passing the query through active records let me share the query and Active record rails code

SELECT (custom_attribute_values.attributable_id) FROM custom_attribute_values WHERE ((“custom_attribute_id” = ’12’ AND “value_string” = ‘Female’) OR (“custom_attribute_id” = ’12’ AND “value_string” = ‘Male’)) INTERSECT SELECT custom_attribute_values.attributable_id FROM custom_attribute_values WHERE ((“custom_attribute_id” = ’17’ AND “value_string” = ‘Widowed’) OR (“custom_attribute_id” = ’17’ AND “value_string” = ‘Divorced’) OR (“custom_attribute_id” = ’17’ AND “value_string” = ‘Never married’) OR (“custom_attribute_id” = ’17’ AND “value_string” = ‘Married’) OR (“custom_attribute_id” = ’17’ AND “value_string” = ‘Separated’))

Here is the time had taken by Postgres SQL

def fetch_members
 begin
   @filter_result = CustomAttributeValue.find_by_sql(segmentation_query).size
 rescue Exception => e
   render_json_response(response, @success, e.message, e)
 end
end

Above is the Rails code

Do you know that how can Speedup my query not to mentioned I have tired the indexing but indexing is very cost paying operation in my case

Answer

For start I would probably try something like this:

SELECT DISTINCT a.attributable_id FROM custom_attribute_values a
  where custom_attribute_id = '12' 
    and value_string IN ('Female', 'Male')
    and EXISTS(
      SELECT 1 FROM custom_attribute_values b
        where custom_attribute_id = '17' 
          and value_string IN ('Widowed', 'Divorced', 'Never married', 'Married', 'Separated')
          and b.attributable_id = a.attributable_id
    )

(there might be some errors I haven’t try to execute the query, but it should give you an idea)

But we will need more information as Frank Heikens said in his comment to the question.