Re: Kudos on the 64 bit PostgreSQL for Windows

> -----Original Message-----
> From: Dann Corbit
> Sent: Friday, October 01, 2010 11:06 AM
> To: 'Joshua J. Kugler'; pgsql-general(at)postgresql(dot)org
> Subject: RE: Kudos on the 64 bit PostgreSQL for Windows
>
> > -----Original Message-----
> > From: Joshua J. Kugler [mailto:joshua(at)eeinternet(dot)com]
> > Sent: Friday, October 01, 2010 10:36 AM
> > To: pgsql-general(at)postgresql(dot)org
> > Cc: Dann Corbit
> > Subject: Re: Kudos on the 64 bit PostgreSQL for Windows
> >
> > On Thursday 30 September 2010, Dann Corbit elucidated thus:
> > > I installed the 64 bit PostgreSQL 9.0 on Windows Server 2008 and it
> > > seems to perform wonderfully. I moved 1.5 million records that are
> 3K
> > > wide from SQL*Server into PostgreSQL and rebuilt 6 indexes all in
> > > less than 6 minutes.
> >
> > > I was thinking of using SQLite for the project,
> > > but I calculate the same data movement would have taken about one
> > > month!
> >
> > This is a bit OT, but were you using transactions in SQLite?
>
> I was using a data movement tool with the SQLite ODBC driver.
> I also tried a script of SQL inserts. Efficiency was the same.
> The actual operation is performed using an INSERT/SELECT.
> In many respects, the comparison is not fair, because we have
> written a custom ODBC driver for PostgreSQL that can use bulk
> mode (COPY) on insert and SQLite does not have a bulk mode.
> We can only do bulk mode operations with database systems that
> have some mechanism for this.
>
> > SQLite
> > can
> > deliver several thousand inserts per second if inside of a
> transaction.
> > If you were only getting a few inserts per second, then you were not
> > using transactions, thus SQLite was on "autocommit" mode, and thus
> > committing after every insert, thus the performance penalty.
>
> For this application, SQLite has no chance to compete. I would not
> want
> to rewrite applications unless there were a dire need. I am not sure
> that it would make sense to do 400 MB in one big transaction either,
> so some kind of fiddling would be needed. PostgreSQL flies like an
> arrow right out of the box. Problem solved.
>

To see what SQLite could do, I made a script that tried some performance options of SQLite. The runtime went down to 2 hours to populate the data, and 2 hours to build the indexes. It was several orders of magnitude faster than the original script and the ODBC procedure, but still a far cry from what PostgreSQL could do {under 6 minutes total}. Note that the table was originally without indexes (they are built afterwards) so that the data loading would go faster.

PRAGMA journal_mode = OFF ;
PRAGMA locking_mode = EXCLUSIVE ;
PRAGMA synchronous = OFF ;
DELETE FROM "Epd";
BEGIN TRANSACTION ;
INSERT INTO "Epd" VALUES ('rn1q1rk1/p4pbp/b2p1np1/1P1pp3/4P3/P1NBBP2/1P4PP/R2QK1NR w KQ -', 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '{482233C0-4B86-4184-B333-000000D0A8BC}', NULL, 7, 1, 3);
INSERT INTO "Epd" VALUES ('r1b2rk1/pp1n2p1/2nbpq1p/3p4/3P4/P2B1N2/1P2NPPP/R1BQ1RK1 w - -', 0, NULL, NULL, NULL, NULL, NULL, NULL, 'Bc2 {8} Nc3 {4} Be3 {1} Ng3 {2}', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '{E9991746-78FE-4372-A3C4-000036CECF6F}', NULL, 5, 5, 5);
INSERT INTO "Epd" VALUES ('r1b1k2r/1pqnbpp1/p2ppn1p/6B1/4PP2/1NN3Q1/PPP3PP/2KR1B1R w kq -', 0, NULL, NULL, NULL, NULL, NULL, NULL, 'Bh4 {11} Bxf6 {12}', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '{DE066550-2C7B-49EA-9599-0000498A1037}', NULL, 5, 10, 6);
INSERT INTO "Epd" VALUES ('rn1q1rk1/p2pbppp/Bp2pn2/8/3PP3/5N2/PP1N1PPP/R1BQ1RK1 b - -', 0, NULL, NULL, NULL, NULL, NULL, NULL, 'Nxa6 {9}', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '{05009024-BA8E-4E33-AA6A-00004C93EB9C}', NULL, 1, 1, 6);
INSERT INTO "Epd" VALUES ('rnb2rk1/ppp1qppp/3b1n2/3p4/3P4/2PBBN2/PP3PPP/RN1Q1RK1 w - -', 0, NULL, NULL, NULL, NULL, NULL, NULL, 'Nbd2 {2} Re1 {3}', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '{AD5D8DCC-44EF-4768-8E8C-00004F630788}', NULL, 3, 0, 1);
INSERT INTO "Epd" VALUES ('r1b1qrk1/ppp3bp/2np2p1/4Pp2/2PN4/1PB3P1/P3PPBP/R2Q1RK1 b - -', 0, NULL, NULL, NULL, NULL, NULL, NULL, 'Nxd4 {3} Nxe5 {2}', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '{BC29DABE-FABE-488D-A5A7-00005451C1A4}', NULL, 1, 1, 3);

INSERT INTO "Epd" VALUES ('r1b1k2r/pp1pnp1p/1q5b/3Pp3/4P2p/1PPQ4/P5PP/RN2KBNR w KQkq -', 0, NULL, NULL, NULL, NULL, NULL, NULL, 'Na3 {3} Nd2 {4}', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '{860F528A-12A1-4707-9594-00005A34FF25}', NULL, 1, 4, 0);
INSERT INTO "Epd" VALUES ('2rq1rk1/pb2bppp/1pn1pn2/3p4/2PP4/PP1B1N2/1B1NQPPP/2R2RK1 b - -', 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '{9AFCE43B-55B4-410E-8EC8-000060FE1ABC}', NULL, 8, 7, 6);
INSERT INTO "Epd" VALUES ('r1b2rk1/ppqnbppp/3ppn2/8/4PB2/N1PB1N2/PP2QPPP/R2R2K1 b - -', 28, 562792157, 16, 'e5 Bg5 h6 Bh4 g5 Bg3 Nc5 Bc2 Be6 Nd2 Rac8 Nb3 a6 Nxc5 Qxc5 Re1 Rfe8 Bb3 Qb6 Qc2 Kg7 Rad1 Red8 Rd3 Rf8 Qd1 Rh8 h3 Rhg8 Bh2 Rgf8 Rf3', NULL, 'e5', NULL, 'Ne5 {1} e5 {63}', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 141, '{3A2F8CFA-41EC-46E7-8044-0000657146C6}', NULL, 16, 15, 33);
INSERT INTO "Epd" VALUES ('r1bqk2r/pp1pbppp/2n2n2/2p1p1B1/2B1P3/2PP1N2/PP3PPP/RN1QK2R b KQkq -', 0, NULL, NULL, NULL, NULL, NULL, NULL, 'O-O {50} d6 {1}', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '{F2488973-50BA-46DE-A391-000069FAE0E6}', NULL, 17, 15, 19);
INSERT INTO "Epd" VALUES ('r4rk1/1bq1bppp/p1nppn2/1p6/4PP2/PNNBBQ2/1PP3PP/R4R1K b - -', 0, NULL, NULL, NULL, NULL, NULL, NULL, 'Rac8 {12} b4 {12} Rab8 {8} Rfe8 {6} Rae8 {3} h5 {3} Rfd8 {2} Nb8 {1} Nd7 {1} Rfb8 {1} Rfc8 {1} g6 {2}', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '{CC164BB2-343D-40DB-AD9B-00006FDE5FA5}', NULL, 24, 12, 16);
INSERT INTO "Epd" VALUES ('r1bq1rk1/pppp1ppp/8/2b1n3/2P1N3/4PB2/PP3PPP/R1BQ1RK1 b - -', 0, NULL, NULL, NULL, NULL, NULL, NULL, 'Be7 {2} d6 {2} Nxf3+ {1} Bb6 {1}', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '{9A323C3F-283A-40CB-A17D-0000724BEF83}', NULL, 1, 0, 2);

One and a half million similar lines follow, and then a commit.
After that, a script build the indexes is run.
In case anyone is wondering, this is a database of chess positions and their analysis.