Sequential scan rather than index scan

Question

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?

Which RDBMS? And can we see the actual query, just the query, and the EXPLAIN, and the DDLs for ALL the tables involved. — Strawberry
– Strawberry, Commented Jul 16, 2015 at 7:47
What proportion of the rent_flat.status values is "3", and what proportion of those records is going to meet the condition on rent_flats.date_added? — David Aldridge
– David Aldridge, Commented Jul 16, 2015 at 8:11
@David around 3.6 million records have status = 3, and the entire result space is around 500 in size. — aceBox
– aceBox, Commented Jul 16, 2015 at 8:42
Please show us the results from EXPLAIN ANALYZE at explain.depesz.com DDL is also needed. — Frank Heikens
– Frank Heikens, Commented Jul 16, 2015 at 9:29

David Aldridge · Accepted Answer · 2015-07-16 10:57:43Z

1

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.

answered Jul 16, 2015 at 10:57

David Aldridge

52.5k8 gold badges73 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Frank Heikens Over a year ago

The real question is, what this age calculation should do. Also in combination with cities.short_period and cities.long_period. It could be simplified by using: current_date - rent_flats.date_added. But this is also impossible to index, current_date changes every day....

David Aldridge Over a year ago

Quite so, but ultimately the predicate should be expressed in that form, or similar, for indexes to be used. It looks to me like the query means "show me the rented properties for which the end of the lease period is today", and combined with only 500 rows being returned, that sounds like an index access on date_added is possible. Not, of course, with the current predicate construction.

Collectives™ on Stack Overflow

Sequential scan rather than index scan

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related