1

I am trying to figure out why Postgres is not using index when querying with ORDER BY clause with a SELECT query and 2 simple LEFT JOIN statements. I will explain my relevant database structure first:

create table public.asset_country_region (
    asset_id int4 not null,
    country_id varchar(3) null,
    name varchar(256) null,
    region varchar(55)  null,
    constraint asset_country_region_pk primary key (asset_id)
);

create unique index asset_country_region_asset_id_idx on public.asset_country_region (asset_id);
create index asset_country_region_region_idx on public.asset_country_region using btree (region nulls last);

create table public.asset_industry (
    asset_id int4 not null,
    fs_sector varchar(1024) null,
    fs_industry varchar(1024) null,
    constraint asset_industry_pk primary key (asset_id)
);
create unique index asset_industry_asset_id_idx on public.asset_industry (asset_id);

create table public.company (
    id uuid primary key default uuid_generate_v4(),
    asset_id integer null unique,
    "name" text null,
    "description" text null
);
create index company_asset_id_idx on public.company using btree ("asset_id");

Now, a simple SELECT on the asset_country_region table works well and uses index for ordering of results:

explain analyze select *
from public.asset_country_region 
order by region desc limit 20 offset 94340;

"QUERY PLAN"
Limit  (cost=5300.34..5301.46 rows=20 width=27) (actual time=23.406..23.412 rows=20 loops=1)
  ->  Index Scan Backward using asset_country_region_region_idx on asset_country_region  (cost=0.42..6547.96 rows=116548 width=27) (actual time=0.075..19.712 rows=94360 loops=1)

However once I try to do a select of all companies and using LEFT JOIN to also get region and industry information if it exists like this:

explain analyze select company.id,
company."name",
company."description",
country.country_id,
country.region,
ind.industry
        from public.company company
        left join public.asset_country_region country
            on company.asset_id = country.asset_id
        left join public.asset_industry ind
            on company.asset_id = ind.asset_id
        order by region desc nulls last limit 20 offset 94340;

Suddenly the query becomes very inefficient and uses ignores the index when ordering:

"QUERY PLAN"
Limit  (cost=34933.39..34933.51 rows=1 width=372) (actual time=1196.696..1217.204 rows=20 loops=1)
  ->  Gather Merge  (cost=25755.05..34933.39 rows=78666 width=372) (actual time=1081.468..1212.852 rows=94360 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Sort  (cost=24755.02..24853.36 rows=39333 width=372) (actual time=1030.703..1043.581 rows=31467 loops=3)
              Sort Key: country.region DESC NULLS LAST
              Sort Method: external merge  Disk: 5696kB
              Worker 0:  Sort Method: external merge  Disk: 4064kB
              Worker 1:  Sort Method: external merge  Disk: 7336kB
              ->  Parallel Hash Left Join  (cost=6900.91..15029.74 rows=39333 width=372) (actual time=424.388..833.288 rows=31467 loops=3)
                    Hash Cond: (company.asset_id = ind.asset_id)
                    ->  Hash Left Join  (cost=4266.33..12291.91 rows=39333 width=334) (actual time=351.987..635.965 rows=31467 loops=3)
                          Hash Cond: (company.asset_id = country.asset_id)
                          ->  Parallel Seq Scan on company  (cost=0.00..3897.33 rows=39333 width=311) (actual time=0.016..27.657 rows=31467 loops=3)
                          ->  Hash  (cost=2012.48..2012.48 rows=116548 width=27) (actual time=350.108..350.110 rows=116548 loops=3)
                                Buckets: 65536  Batches: 4  Memory Usage: 2256kB
                                ->  Seq Scan on asset_country_region country  (cost=0.00..2012.48 rows=116548 width=27) (actual time=0.038..99.803 rows=116548 loops=3)
                    ->  Parallel Hash  (cost=1791.48..1791.48 rows=67448 width=42) (actual time=69.346..69.347 rows=38220 loops=3)
                          Buckets: 131072  Batches: 1  Memory Usage: 9920kB
                          ->  Parallel Seq Scan on asset_industry ind  (cost=0.00..1791.48 rows=67448 width=42) (actual time=0.040..29.784 rows=38220 loops=3)
Planning Time: 0.442 ms
Execution Time: 1225.544 ms

I have been trying to figure this out with no success so far. If anybody could give me some ideas I would really appreciate it.

I should also mention that asset_country_region and asset_industry are materialized views (with data) if that's relevant. I didn't want to include entire SQL to create these materialized views as it's a bit longwinded.

6
  • 1
    If it did use the index, what do you think the rest of the plan would then look like? Considering that rows being returned may not exist in the left-joined-to table, how would it use an index on it to order the rows?
    – jjanes
    Feb 28 at 3:08
  • @jjanes I was thinking it would be able to order those that exist in the left joined to table and the rest would be appended to the end similar to NULLS LAST ordering. I guess this won't work though. Is there some other way to optimise this query other than denormalisation? Feb 28 at 3:17
  • 1
    If you could specify where to start as a where filter instead of using offset it would be much better. leopard.in.ua/2014/10/11/postgresql-paginattion
    – Eelke
    Feb 28 at 8:33
  • If you convert the left joins to inner joins, do you get a better plan? I wonder if the planner can use the index even for that simpler case.
    – jjanes
    Feb 28 at 13:13
  • @jjanes No. With INNER JOINs the plan still looks exactly as shown above. Not sure what is wrong. Feb 28 at 15:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.