CODEWITHSUNDEEP
postgresqljoinoptimization
aceBox
aceBox

Reputation: 731

Sequential scan rather than index scan

I have a bunch of tables in postgresql and I run a query as follows

 SELECT DISTINCT ON ...some stuff... 
    FROM "rent_flats" INNER JOIN "rent_flats_linked_users" 
            ON "rent_flats_linked_users"."rent_flat_id" = "rent_flats"."id" 
        INNER JOIN "users" 
            ON "users"."id" = rent_flats_linked_users"."user_id" 
        INNER JOIN "owners" 
            ON "owners"."id" = "users"."profile_id" AND "users"."profile_type" = 'Owner' 
        INNER JOIN "phone_numbers" 
            ON "phone_numbers"."person_id" = "owners"."id" AND "phone_numbers"."person_type" = 'Owner' 
        INNER JOIN "phone_number_categories" 
            ON "phone_number_categories"."id" = "phone_numbers"."phone_number_category_id" 
        INNER JOIN "localities" 
            ON "localities"."id" = "rent_flats"."locality_id" 
        INNER JOIN "regions" 
            ON "regions"."id" = "localities"."region_id" 
        INNER JOIN "cities" 
            ON "cities"."id" = "regions"."city_id" 
        INNER JOIN "property_types" 
            ON "property_types"."id" = "rent_flats"."property_type_id" 
        INNER JOIN "apartment_types" 
            ON "apartment_types"."id" = "rent_flats"."apartment_type_id" 
    WHERE "rent_flats"."status" = 3 
        AND (((extract(epoch from age(current_date,rent_flats.date_added))/86400)::int) IN (cities.short_period,cities.long_period)) 
        AND (phone_number_categories.name IN ('SMS','SMS & Mobile')) 
    ORDER BY rf_id, phone_numbers.priority ASC

Note: The rent_flats table contains around 5 million rows, and rent_flats_linked_users contains around 600k rows and users contains 350k rows.Other tables are small in size.

The query takes about 6.8 secs to execute and the explain analyses shows that around 50% of the total time goes in sequential scans of the rent_flats, users and rent_flats_linked_users tables and the other 30% in Hash joins.

On setting seq_scan to off...the query takes even longer to ~11 secs (in this case Hash and Hash join take upto 97.5% of the time)

Here's the explain query plan analyses. I have put indices on the fields involved in the inner joins as well as on fields involved in the filters like phone_numbers.priority and cities.short_period and cities.long_period. But I still get a sequential scan. What can be the reasons and possible solutions to fasten the query?

Upvotes: 0

Views: 173

Answers (1)

David Aldridge
David Aldridge

Reputation: 52396

I suspect that if there is a part of that query worth optimising then it is this:

(((extract(epoch from age(current_date,rent_flats.date_added))/86400)::int) IN (cities.short_period,cities.long_period))

You really need to turn that into something like:

rent_flats.date_added in (...)

Then you can index date_added, and maybe index (date_added, status).

the next step would be to make sure that the join columns are indexed.

Upvotes: 1

Related Questions