Migrate MS SQLServer to PostgreSQL

[thenetstriker]

What is the best way to migrate a MSSQL Photosupreme database to Postgres?

[Hert]

You can use the data exchange tool for that
viewtopic.php?f=57&t=23417

[thenetstriker]

Thanks Hert, it works for the main database. Can I also migrate the tumbs database with this tool or do I have to recreate all thumbnails? (I got an error that the idUser table was not found when I tried to migrate using the tool)

[Hert]

You'll need to rebuild the thumbnails.

For my info; Any reason why you switch from SQLServer to Postgresql?

[thenetstriker]

Thanks for the quick reply. I'm migrating simply because Postgres is free and runs on a free Linux server.

I also have another problem after the migration. All the pictures are not found for some reason. I tried to relocate one of the pictures and that solved the problem but I cannot see any difference in the database in the path and filename in the tables idcatalogitem and idfilepath. And I cannot relocate every picture manually. Do you have an idea what could cause this problem?

Edit: I just found out that I can relocate the whole main folder and then the pictures are displayed again.

[thenetstriker]

I have another question about the Postgres Database. I found out that a simple select query to the v_prop view take 16 times longer than on the ms sql server. (16 seconds instead of 1) Is this a known problem or can this be improved with additional indices or settings in the postgres server?

[Hert]

On my database it takes 300ms to query and fetch the full v_prop view (about 2500 record). But even that's irrelevant as the application will never query v_prop like that.
If your database performs poorly then make sure you configured database according to the installation instructions. The Photo Supreme application performs comparable between Postgresql and SQLServer on the same hardware.

[thenetstriker]

I'm using the view for my Android application to get a list of the image properties including the image count. I found the Postgres manual and I changed the memory settings accordingly. The view did get a little bit faster (from 16 to 12 seconds with 866 rows) but it is still not as fast as on the MS SQL server. (under 1 second)

The server runs virtualized on the exact same hardware as the windows server and both servers have 16 GB ram. Do you have any other idea why the view is slow on my server?

Here is the query plan of the view, maybe that helps to identify the problem:

Code: Select all

QUERY PLAN
Seq Scan on idprop p  (cost=0.00..13917122.06 rows=866 width=430) (actual time=671.250..16139.636 rows=866 loops=1)
  SubPlan 2
    ->  Aggregate  (cost=15614.35..15614.36 rows=1 width=8) (actual time=17.546..17.546 rows=1 loops=866)
          ->  HashAggregate  (cost=14962.77..15252.36 rows=28959 width=32) (actual time=17.360..17.490 rows=180 loops=866)
                Group Key: d.catalogitemguid
                ->  Merge Join  (cost=632.30..14431.83 rows=212375 width=32) (actual time=16.009..17.266 rows=217 loops=866)
                      Merge Cond: ((d.guid)::text = (lp_1.guid)::text)
                      ->  Index Scan using idcatalogitemdefinition_idxguid on idcatalogitemdefinition d  (cost=0.42..10359.31 rows=102009 width=64) (actual time=0.007..11.004 rows=53574 loops=866)
                      ->  Sort  (cost=631.88..635.88 rows=1601 width=118) (actual time=0.279..0.296 rows=110 loops=866)
                            Sort Key: lp_1.guid
                            Sort Method: quicksort  Memory: 25kB
                            ->  CTE Scan on lowerparent lp_1  (cost=498.64..530.66 rows=1601 width=118) (actual time=0.010..0.273 rows=2 loops=866)
                                  CTE lowerparent
                                    ->  Recursive Union  (cost=0.28..498.64 rows=1601 width=68) (actual time=0.008..0.271 rows=2 loops=866)
                                          ->  Index Scan using idxpropguiduserguid on idprop px  (cost=0.28..8.29 rows=1 width=68) (actual time=0.006..0.007 rows=1 loops=866)
                                                Index Cond: ((guid)::text = (p.guid)::text)
                                          ->  Hash Join  (cost=0.33..45.83 rows=160 width=68) (actual time=0.233..0.242 rows=1 loops=918)
                                                Hash Cond: ((px_1.parentguid)::text = (lp.guid)::text)
                                                ->  Seq Scan on idprop px_1  (cost=0.00..40.66 rows=866 width=68) (actual time=0.003..0.096 rows=866 loops=918)
                                                ->  Hash  (cost=0.20..0.20 rows=10 width=118) (actual time=0.022..0.022 rows=1 loops=918)
                                                      Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                                      ->  WorkTable Scan on lowerparent lp  (cost=0.00..0.20 rows=10 width=118) (actual time=0.020..0.020 rows=1 loops=918)
  SubPlan 4
    ->  Limit  (cost=456.17..456.18 rows=1 width=122) (actual time=0.392..0.393 rows=1 loops=866)
          CTE higherparent
            ->  Recursive Union  (cost=0.28..453.89 rows=101 width=159) (actual time=0.012..0.378 rows=2 loops=866)
                  ->  Index Scan using idxpropguiduserguid on idprop px_2  (cost=0.28..8.29 rows=1 width=72) (actual time=0.009..0.010 rows=1 loops=866)
                        Index Cond: ((guid)::text = (p.guid)::text)
                  ->  Hash Join  (cost=0.33..44.36 rows=10 width=159) (actual time=0.162..0.223 rows=0 loops=1375)
                        Hash Cond: ((px_3.guid)::text = (hp.parentguid)::text)
                        ->  Seq Scan on idprop px_3  (cost=0.00..40.66 rows=866 width=68) (actual time=0.003..0.108 rows=866 loops=1375)
                        ->  Hash  (cost=0.20..0.20 rows=10 width=240) (actual time=0.002..0.002 rows=1 loops=1375)
                              Buckets: 1024  Batches: 1  Memory Usage: 9kB
                              ->  WorkTable Scan on higherparent hp  (cost=0.00..0.20 rows=10 width=240) (actual time=0.000..0.000 rows=1 loops=1375)
          ->  Sort  (cost=2.28..2.29 rows=1 width=122) (actual time=0.390..0.390 rows=1 loops=866)
                Sort Key: hp_1.idlevel
                Sort Method: quicksort  Memory: 25kB
                ->  CTE Scan on higherparent hp_1  (cost=0.00..2.27 rows=1 width=122) (actual time=0.015..0.382 rows=2 loops=866)
                      Filter: ((guid)::text = (p.guid)::text)
Planning Time: 1.167 ms
JIT:
  Functions: 54
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 6.648 ms, Inlining 11.450 ms, Optimization 390.815 ms, Emission 211.769 ms, Total 620.682 ms
Execution Time: 16146.736 ms

Edit: I found out that the view on the MS Sql server is built differently. On the MS SQL server the view uses two functions (PropChildList and PropParentList) to get the photocount and the category guid and the Postgres server does not use those functions. I've now added the funtctions to the Postgres database and changed the view accordingly. With those changes the query is now almost as fast as on the Ms Sql server. (1.5 seconds compared to 0.8 seconds)

Is there a reason why the view is built differently in the postgres database?

[Hert]

As mentioned before, it takes a fraction of a second here to get the results of v_prop.

Did you reboot the server?
Did you try a vacuum?
Did you try a reindex?

[thenetstriker]

As mentioned before, it takes a fraction of a second here to get the results of v_prop.

Did you reboot the server?
Did you try a vacuum?
Did you try a reindex?

Yes I've tried all that, but that also didn't help. But did you see the "Edit" part I've added to my last comment? I found out that the view on the postgres server is different from the MsSql one. I've managed to change the postgres view so that is also uses those two functions (PropChildList and PropParentList) and now the query is almost as fast as on the MsSql Server. (It is now maybe just half a second slower than on the MsSql server)

I've built the Postgres database using the latest version of Photosupreme V5. Do you have an idea why the view differs from the one on the MsSql server? Maybe this performance optimiziation did not make it into the Postgres version of Photosupreme?

[Hert]

Do you have an idea why the view differs from the one on the MsSql server?

The implementation that PSU uses *is* the optimization. On Postgresql the implementation as used in MS-SQLServer takes 12 seconds to execute and the implementation that PSU currently uses in Postgresql takes a fraction of a second to execute (as I've stated twice).

If you found a solution that works better for your tool then use that instead.