1

I have cloned a schema in Postgres along with the data. The process I used for cloning the schema is mentioned below. The issue is that the same query on the same table is taking more time to execute on the cloned schema than on the original schema. Please note that both the tables have the same data on both the schema.

Execution on original schema is almost 3 times faster than on the cloned schema

I have even rebuilt the index on the cloned schema

How I cloned the schema:

  1. pg_dump of the original schema
  2. sed command on the dump file to change the schema name
  3. restored the dump file as a new schema

Is the process I have followed to clone the schema right? Does this process have anything to do with the query taking a different amount of time?

The query planner also chooses two different approaches. Below I have attached the query planner's approaches.

Planner on the Original schema:

Planner EXPLAIN ANALYZE on original schema

Nested Loop  (cost=0.98..780.24 rows=1 width=573)
  ->  Nested Loop  (cost=0.70..779.82 rows=1 width=577)
        ->  Nested Loop  (cost=0.42..779.49 rows=1 width=573)
              Join Filter: (photo_photo.photo_type_id = common_phototype.id)
              ->  Nested Loop  (cost=0.42..778.45 rows=1 width=573)
                    Join Filter: (photo_photo.category_id = common_category.id)
                    ->  Nested Loop  (cost=0.42..777.36 rows=1 width=573)
                          ->  Seq Scan on store_store  (cost=0.00..381.23 rows=1 width=4)
                                Filter: ((store_id)::text = '********'::text)
                          ->  Index Scan using photo_photo_store_id_0002dcc4 on photo_photo  (cost=0.42..396.13 rows=1 width=573)
                                Index Cond: (store_id = store_store.id)
                                Filter: ((NOT ********) AND (NOT ******) AND is_valid AND ((timezone('UTC'::text, "timestamp"))::date = '********'::date))
                    ->  Seq Scan on common_category  (cost=0.00..1.07 rows=1 width=4)
                          Filter: ((name)::text = '********'::text)
              ->  Seq Scan on common_phototype  (cost=0.00..1.02 rows=1 width=4)
                    Filter: ((name)::text = '*********'::text)
        ->  Index Only Scan using auth_user_pkey on auth_user  (cost=0.28..0.32 rows=1 width=4)
              Index Cond: (id = photo_photo.user_id)
  ->  Index Scan using user_profile_pkey on user_profile  (cost=0.28..0.41 rows=1 width=4)
        Index Cond: (user_id = photo_photo.user_id)
        Filter: (user_type = ANY ('*******'::integer[]))

Planner on cloned schema:

Planner EXPLAIN ANALYZE on cloned schema

Nested Loop  (cost=47.28..2629.90 rows=1 width=599)
  ->  Nested Loop  (cost=47.00..2628.42 rows=3 width=599)
        ->  Nested Loop  (cost=46.87..2627.44 rows=6 width=599)
              Join Filter: (photo_photo.category_id = common_category.id)
              ->  Seq Scan on common_category  (cost=0.00..1.07 rows=1 width=4)
                    Filter: ((name)::text = '*****'::text)
              ->  Nested Loop  (cost=46.87..2625.94 rows=34 width=599)
                    ->  Hash Join  (cost=27.45..64.34 rows=11 width=8)
                          Hash Cond: (auth_user.id = user_profile.user_id)
                          ->  Seq Scan on auth_user  (cost=0.00..32.48 rows=1148 width=4)
                          ->  Hash  (cost=27.31..27.31 rows=11 width=4)
                                ->  Seq Scan on user_profile  (cost=0.00..27.31 rows=11 width=4)
                                      Filter: (user_type = ANY ('*******'::integer[]))
                    ->  Bitmap Heap Scan on photo_photo  (cost=19.42..232.77 rows=10 width=599)
                          Recheck Cond: (user_id = auth_user.id)
                          Filter: ((NOT ******) AND (NOT *******) AND is_valid AND ((timezone('UTC'::text, "timestamp"))::date = '********'::date))
                          ->  Bitmap Index Scan on photo_photo_user_id_b6d9e71a  (cost=0.00..19.42 rows=2064 width=0)
                                Index Cond: (user_id = auth_user.id)
        ->  Index Scan using common_phototype_pkey on common_phototype  (cost=0.13..0.15 rows=1 width=4)
              Index Cond: (id = photo_photo.photo_type_id)
              Filter: ((name)::text = '*******'::text)
  ->  Index Scan using store_store_pkey on store_store  (cost=0.28..0.48 rows=1 width=4)
        Index Cond: (id = photo_photo.store_id)
        Filter: ((store_id)::text = '********'::text)
jjanes
  • 37,812
  • 5
  • 27
  • 34
ganesh prasad
  • 61
  • 1
  • 4
  • 1
    Did you run `analyze` after importing the dump to update the statistics for the optimizer? –  Aug 12 '22 at 05:32
  • Could you please share the results from explain (analyze, verbose, buffers) for these queries? – Frank Heikens Aug 12 '22 at 06:08
  • @a_horse_with_no_name , Yes i have done analyze on the table and found no improvement – ganesh prasad Aug 12 '22 at 07:18
  • @FrankHeikens added the links for explain – ganesh prasad Aug 12 '22 at 11:02
  • @ganeshprasad: only one of the links is working, could you fix the other one? – Frank Heikens Aug 12 '22 at 14:35
  • The row estimate on demo_mt_1.user_profile is obviously bad on the slow plan. We can't tell if it is also bad on the fast one, as it accesses the table by a different means. Could you show the result of `explain analyze select user_id from pgindo.user_profile where user_type = ANY ('*******'::integer[])` so we can see what the planner thinks of that? – jjanes Aug 12 '22 at 16:07
  • @jjanes On the slow plan when executed individually, the planner is just doing a seq scan and it is much faster. QUERY PLAN ------------------------------------------------------------------------------------------------------------- Seq Scan on user_profile (cost=0.00..56.35 rows=1025 width=4) (actual time=0.022..0.795 rows=1019 loops=1) Filter: (user_type = ANY ('*****'::integer[])) Planning time: 0.058 ms Execution time: 0.891 ms – ganesh prasad Aug 16 '22 at 06:27

0 Answers0