'Postgres query performance improvement

I am a newbie to database optimisations, The table data I have is around 29 million rows, I am running on Pgadmin to do select * on the rows and it takes 9 seconds. What can I do to optimize performance?

SELECT 
  F."Id", 
  F."Name", 
  F."Url", 
  F."CountryModel", 
  F."RegionModel", 
  F."CityModel", 
  F."Street", 
  F."Phone", 
  F."PostCode", 
  F."Images", 
  F."Rank", 
  F."CommentCount", 
  F."PageRank", 
  F."Description", 
  F."Properties", 
  F."IsVerify", 
  count(*) AS Counter 
FROM 
  public."Firms" F, 
  LATERAL unnest(F."Properties") AS P 
WHERE 
  F."CountryId" = 1 
  AND F."RegionId" = 7 
  AND F."CityId" = 4365 
  AND P = ANY (ARRAY[126, 128]) 
  AND F."Deleted" = FALSE 
GROUP BY 
  F."Id" 
ORDER BY 
  Counter DESC, 
  F."IsVerify" DESC, 
  F."PageRank" DESC OFFSET 10 ROWS FETCH FIRST 20 ROW ONLY

Thats my query plan

"  ->  Sort  (cost=11945.20..11948.15 rows=1178 width=369) (actual time=8981.514..8981.515 rows=30 loops=1)"
"        Sort Key: (count(*)) DESC, f.""IsVerify"" DESC, f.""PageRank"" DESC"
"        Sort Method: top-N heapsort  Memory: 58kB"
"        ->  HashAggregate  (cost=11898.63..11910.41 rows=1178 width=369) (actual time=8981.234..8981.305 rows=309 loops=1)"
"              Group Key: f.""Id"""
"              Batches: 1  Memory Usage: 577kB"
"              ->  Nested Loop  (cost=7050.07..11886.85 rows=2356 width=360) (actual time=79.408..8980.167 rows=322 loops=1)"
"                    ->  Bitmap Heap Scan on ""Firms"" f  (cost=7050.06..11716.04 rows=1178 width=360) (actual time=78.414..8909.649 rows=56071 loops=1)"
"                          Recheck Cond: ((""CityId"" = 4365) AND (""RegionId"" = 7))"
"                          Filter: ((NOT ""Deleted"") AND (""CountryId"" = 1))"
"                          Heap Blocks: exact=55330"
"                          ->  BitmapAnd  (cost=7050.06..7050.06 rows=1178 width=0) (actual time=70.947..70.947 rows=0 loops=1)"
"                                ->  Bitmap Index Scan on ""IX_Firms_CityId""  (cost=0.00..635.62 rows=58025 width=0) (actual time=11.563..11.563 rows=56072 loops=1)"
"                                      Index Cond: (""CityId"" = 4365)"
"                                ->  Bitmap Index Scan on ""IX_Firms_RegionId""  (cost=0.00..6413.60 rows=588955 width=0) (actual time=57.795..57.795 rows=598278 loops=1)"
"                                      Index Cond: (""RegionId"" = 7)"
"                    ->  Function Scan on unnest p  (cost=0.00..0.13 rows=2 width=0) (actual time=0.001..0.001 rows=0 loops=56071)"
"                          Filter: (p = ANY ('{126,128}'::integer[]))"
"                          Rows Removed by Filter: 2"
"Planning Time: 0.351 ms"
"Execution Time: 8981.725 ms"```
postgresql


Solution 1:[1]

Create a GIN index on F."Properties",

create index on "Firms" using gin ("Properties");

then add a clause to your WHERE

...
AND P = ANY (ARRAY[126, 128]) 
AND "Properties" && ARRAY[126, 128]
....

That added clause is redundant to the one preceding it, but the planner is not smart enough to reason through that so you need to make it explicit.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 jjanes

Related Questions