Ygthkb

I have 2 instances of free tier rds postgresql db.t2.micro on same zone us-west2 region on 2 different accounts, using defaults at creation, Original and Copy.

Original is significantly slower than Copy.

I created a single table on the Copy using pg_dump / pg_restore after one contained on the Original which contains 100s of tables.

One that one table toto that interests me, that lives both on Original and Copy, I got a query that runs in 4s on Original and 172ms on the Copy.
I thought as an index issue at first, that table contains only one.

I did a VACCUM FULL, REINDEX table, REINDEX index to no avail.

I'm unable to get the slightest idea as to why there is such a massive discrepancy, nor have a sense as to where to begin to look at:

the only differences between the 2 instances of the db are:

1. the Copy has only one table and the Original has a lot of them, I'm unaware that it could lead to such a massive penalty but I'm no db pro




2. the table in question in Original contains one supplemental column I filled, but I can't say that this lead to the difference in performance

explain on the Copy

Sort  (cost=51484.50..51484.50 rows=1 width=80) (actual time=172.788..172.789 rows=1 loops=1)

  Output: "TICKER", "ISIN", "SOURCE", "TICKER", "SCORE", "TIME_STAMP_SOURCE"

  Sort Key: toto."TICKER"

  Sort Method: quicksort  Memory: 25kB

  ->  Gather  (cost=1000.00..51484.49 rows=1 width=80) (actual time=162.039..172.854 rows=1 loops=1)

        Output: "TICKER", "ISIN", "SOURCE", "TICKER", "SCORE", "TIME_STAMP_SOURCE"

        Workers Planned: 2

        Workers Launched: 2

        ->  Parallel Seq Scan on public.toto  (cost=0.00..50484.39 rows=1 width=80) (actual time=161.547..164.635 rows=0 loops=3)

              Output: "TICKER", "ISIN", "SOURCE", "TICKER", "SCORE", "TIME_STAMP_SOURCE"

              Filter: ((toto."TIME_STAMP_SOURCE" = '2019-02-14'::text) AND (((toto."TICKER")::text = 'AAOI US Equity'::text) OR (toto."ISIN" = 'AAOI US Equity'::text)))

              Rows Removed by Filter: 196468

              Worker 0: actual time=159.843..159.843 rows=0 loops=1

              Worker 1: actual time=162.985..162.986 rows=0 loops=1

Planning time: 0.118 ms

Execution time: 172.894 ms

the same on Original

Sort  (cost=49649.78..49649.78 rows=1 width=80) (actual time=4532.986..4532.987 rows=1 loops=1)

  Output: "TICKER", "ISIN", "SOURCE", "TICKER", "SCORE", "TIME_STAMP_SOURCE"

  Sort Key: toto."TICKER"

  Sort Method: quicksort  Memory: 25kB

  ->  Gather  (cost=1000.00..49649.77 rows=1 width=80) (actual time=4531.345..4533.023 rows=1 loops=1)

        Output: "TICKER", "ISIN", "SOURCE", "TICKER", "SCORE", "TIME_STAMP_SOURCE"

        Workers Planned: 2

        Workers Launched: 2

        ->  Parallel Seq Scan on public.toto  (cost=0.00..48649.67 rows=1 width=80) (actual time=4419.506..4528.946 rows=0 loops=3)

              Output: "TICKER", "ISIN", "SOURCE", "TICKER", "SCORE", "TIME_STAMP_SOURCE"

              Filter: ((toto."TIME_STAMP_SOURCE" = '2019-02-14'::text) AND (((toto."TICKER")::text = 'AAOI US Equity'::text) OR (toto."ISIN" = 'AAOI US Equity'::text)))

              Rows Removed by Filter: 196468

              Worker 0: actual time=4198.182..4526.499 rows=1 loops=1

              Worker 1: actual time=4529.205..4529.205 rows=0 loops=1

Planning time: 0.121 ms

Execution time: 4533.065 ms