2

I have searched many articles in Google about the performance of PostgreSQL vs MS SQL 2008 R2 and found that many of those articles say the performance of PostgreSQL is better than that of MSSQL 2008 R2. So, I make a real test with my company's real case.

Hardware: I build two vmware guests in ESXi 5.5, each of the guest has the same virtual hardware specification, which include (I only list the key items):

  • Memory: 4GB
  • CPUs: one virtual socket with two cores
  • Virtual Disk: 50GB for Ubuntu server and 200GB for Windows 7 (well, as you know, Microsoft always consumes more storage space)

VM Host (a single box ):

  • RAM: 16GB with intel-i5 2 cores with hyper threading
  • HD: 1TGB 2.5"

Software:

  • Windows 7 Ultima + MS SQL Server 2008 R2, all with default parameters
  • Ubuntu Linux Server 12.04 + PostgreSQL 9.3, all with default parameters
  • DB tools: Db Visualizer 9.0.9 (on another laptop)
  • JDBC:
    • PostgreSQL: postgresql-9.3-1101.jdbc4
    • MSSQL 2008R2: sqljdbc_4.0.2206.100_cht.tar

Table schemas (same on PostgreSQL and MSSQL 2008 R2, except the naming convention)

I have three tables as below:

CREATE TABLE gourmet (
  vendor_url CHARACTER VARYING(200),
  ticket_price CHARACTER VARYING(8000),
  tour_advice CHARACTER VARYING(8000),
  gourmet_name_c CHARACTER VARYING(180),
  tel_ext CHARACTER VARYING(5),
  gourmet_name_e CHARACTER VARYING(60),
  arrangement CHARACTER VARYING(8000),
  data_source CHARACTER VARYING(200),
  open_time CHARACTER VARYING(8000),
  vendor_name CHARACTER VARYING(60),
  gps_x CHARACTER VARYING(20),
  gourmet_id CHARACTER VARYING(36) NOT NULL,
  gps_y CHARACTER VARYING(20),
  updated_by CHARACTER VARYING(20),
  recom_level DOUBLE PRECISION,
  fax_num CHARACTER VARYING(10),
  address_c CHARACTER VARYING(240),
  fax_area_code CHARACTER VARYING(4),
  long_desc CHARACTER VARYING(8000),
  for_search_only BOOLEAN,
  status CHARACTER VARYING(1),
  category CHARACTER VARYING(3),
  area_code CHARACTER VARYING(4),
  county CHARACTER VARYING(3),
  email CHARACTER VARYING(40),
  updated_date TIMESTAMP(6) WITHOUT TIME ZONE,
  data_source_url CHARACTER VARYING(200),
  tour_duration CHARACTER VARYING(400),
  short_name CHARACTER VARYING(180),
  created_by CHARACTER VARYING(20),
  created_date TIMESTAMP(6) WITHOUT TIME ZONE,
  town CHARACTER VARYING(3),
  address_e CHARACTER VARYING(80),
  parking_info CHARACTER VARYING(8000),
  remark CHARACTER VARYING(8000),
  location_info CHARACTER VARYING(8000),
  tel_no CHARACTER VARYING(10),
  CONSTRAINT gourmet_pk PRIMARY KEY (gourmet_id)
);

CREATE INDEX _dta_index_gourmet_5_574625090__k9_k4_k1_k10_k31_k2_11_14_15_33 ON gourmet (
  county,
  status,
  gourmet_id,
  town,
  vendor_url,
  gourmet_name_c
);

CREATE INDEX  _dta_index_gourmet_7_1796201449__k9_k1_k2 ON   gourmet
(
  county,
  gourmet_id,
  gourmet_name_c
);

CREATE INDEX idx_grourmet_status ON gourmet (
  status
);
=======================================================

CREATE TABLE photos (
  source CHARACTER VARYING(100),
  caption CHARACTER VARYING(100),
  display_order INTEGER,
  main_photo BOOLEAN,
  bytes_original BYTEA,
  folder_id CHARACTER VARYING(36),
  photo_id INTEGER DEFAULT nextval('photos_photo_id_seq'::regclass) NOT NULL,
  bytes_thumb BYTEA,
  CONSTRAINT photos_pk PRIMARY KEY (photo_id)
);

CREATE INDEX  _dta_index_photos_5_1925581898__k2_k5_1 ON  photos  (
  folder_id,
  main_photo
);

CREATE INDEX idx_photos_folder_id ON photos (
  folder_id
);

CREATE INDEX _dta_index_photos_5_1925581898__k5_k2_k1 ON  photos  (
  main_photo,
  folder_id,
  photo_id
);

====================================================

CREATE TABLE counters (
  item_id CHARACTER VARYING(36) NOT NULL,
  counter_name CHARACTER VARYING(30) NOT NULL,
  target_date CHARACTER(10) NOT NULL,
  total_hit BIGINT,
  CONSTRAINT counters_pk PRIMARY KEY (item_id, counter_name, target_date)
);

There are 4108 records in table gourmet, 37451 records in table photos, and 11659353 records in table counters.

For avoiding the resultset's transmission consuming the timing, so I just count the record number.

SQL statement used in PostgreSQL:

SELECT count(*) FROM (
  SELECT        a.gourmet_id item_id, a.gourmet_name_c item_name, a.vendor_url external_url,  'fnf-item.aspx?pid=' || a.gourmet_id fun_taiwan_url,                    
        b.photo_id,coalesce(SUM(c.total_hit),0) hit_count,to_char(a.created_date, 'YYYY/MM/DD HH24:MI:SS') created_date,                                                  
        '1' is_gourmet, a.for_search_only, a.area_code || '-' || a.tel_no tel_no, a.tel_ext, a.county, a.town, a.address_c , 'gourmet.png' icon_name    ,            
        now() as updated_date, 'NonOrderScheduler' as updated_by                                                                                                                                         
  FROM      gourmet a                                                                                                                                                                                          
  LEFT JOIN photos b ON b.folder_id=a.gourmet_id AND b.main_photo='1'                                                                                       
  LEFT JOIN counters c ON c.item_id=a.gourmet_id AND c.counter_name='FnfHit'                                                                              
  WHERE     a.status='A'                                                                                                                               
  GROUP BY  a.gourmet_id, a.gourmet_name_c, length(a.vendor_url),a.vendor_url, 'fnf-item.aspx?pid=' || a.gourmet_id,b.photo_id, to_char(a.created_date,'YYYY/MM/DD HH24:MI:SS'),
        a.for_search_only, a.area_code || '-' || a.tel_no, a.tel_ext,a.county, a.town, a.address_c
) t

SQL used in MSSQL 2008 R2:

CHECKPOINT; 
GO 
DBCC DROPCLEANBUFFERS; --Clean Query Cache
GO
SELECT count(*) FROM (
  SELECT        a.GourmetId ItemId, a.GourmetNameC ItemName, a.VendorUrl ExternalUrl,  'fnf-item.aspx?pid=' + a.GourmetId FunTaiwanUrl,                    
        b.PhotoId,ISNULL(SUM(c.TotalHit),0) HitCount, CONVERT(VARCHAR,a.CreatedDate,111) CreatedDate,                                                  
        '1' IsGourmet, a.ForSearchOnly, a.AreaCode + '-' + a.TelNo TelNo, a.TelExt, a.County, a.Town, a.AddressC , 'gourmet.png' IconName   ,            
        GetDate() as UpdatedDate, 'NonOrderScheduler' as UpdatedBy                                                                                                                                       
  FROM      Gourmet a                                                                                                                                                                                          
  LEFT JOIN Photos b ON b.FolderId=a.GourmetId AND b.MainPhoto=1                                                                                       
  LEFT JOIN Counters c ON c.ItemId=a.GourmetId AND c.CounterName='FnfHit'                                                                              
  WHERE     a.[Status]='A'                                                                                                                               
  GROUP BY  a.GourmetId, a.GourmetNameC, len(a.VendorUrl),a.VendorUrl, 'fnf-item.aspx?pid=' + a.GourmetId,b.PhotoId,    CONVERT(VARCHAR,a.CreatedDate,111),
        a.ForSearchOnly, a.AreaCode + '-' + a.TelNo, a.TelExt,a.County, a.Town, a.AddressC 
) t

Actually, the two SQL statements are identical , except the syntax and function.

Test method:

I execute the SQL statement in DbVisualizer from another laptop. There are just the two vmware guests in the vmware host. When I start to test PostgreSQL, the Windows 7 Ultimate keeps no foreground software running, and vice versa on Ubuntu server.

With the same table schemas, same records, and the same SQL statement in MS SQL Server 2008 on Windows 7 Ultimate, I found the result is far different from those articles who say PostgreSQL is faster that of MSSQL2008R2.

For counting the resultset size, MSSQL2008 R2 spends 0.016 seconds, and PostgreSQL spends 132 seconds. As you can see, before executing SQL on MSSQL2008R2, I clean its query cache first, but I didn't clean query cache on PostgreSQL.

My question is, how should I tune PostgreSQL so that it can query faster than that of MSSQL2008 R2 in this case ?

I know some of you may ask me to tune the SQL statement with explain analyze command. However, basically, I don't wanna tune the SQL statement unless the server(OS + PostgreSQL) parameters have been tuned to the best situation because I am planing to moving all data in my company from MSSQL2008R2 to PostgreSQL. So, it is impossible to tune each SQL statement for every system.

If by tuning the OS parameters and PostgreSQL parameters can solve my problem, I prefer not to tune SQL statements in the short time. You know it will be a huge job.

Thank you for your help.

Attached the explain analyze result of PostgreSQL

    HashAggregate  (cost=305063.90..328086.20 rows=920892 width=126) (actual time=4595.501..4598.155 rows=4019 loops=1)
      ->  Nested Loop Left Join  (cost=450.60..272832.68 rows=920892 width=126) (actual time=4.304..2567.521 rows=1690393 loops=1)
            ->  Hash Right Join  (cost=450.04..1230.97 rows=4132 width=118) (actual time=4.223..20.423 rows=4019 loops=1)
                  Hash Cond: ((b.folder_id)::text = (a.gourmet_id)::text)
                  ->  Bitmap Heap Scan on photos b  (cost=200.44..848.93 rows=12149 width=13) (actual time=0.896..5.782 rows=12166 loops=1)
                        Filter: main_photo
                        ->  Bitmap Index Scan on _dta_index_photos_5_1925581898__k5_k2_k1  (cost=0.00..197.41 rows=12149 width=0) (actual time=0.840..0.840 rows=12166 loops=1)
                              Index Cond: (main_photo = true)
                  ->  Hash  (cost=199.36..199.36 rows=4019 width=114) (actual time=3.305..3.305 rows=4019 loops=1)
                        Buckets: 1024  Batches: 1  Memory Usage: 603kB
                        ->  Seq Scan on gourmet a  (cost=0.00..199.36 rows=4019 width=114) (actual time=0.012..1.768 rows=4019 loops=1)
                              Filter: ((status)::text = 'A'::text)
                              Rows Removed by Filter: 90
            ->  Index Scan using counters_pk on counters c  (cost=0.56..60.72 rows=223 width=16) (actual time=0.029..0.132 rows=421 loops=4019)
                  Index Cond: (((item_id)::text = (a.gourmet_id)::text) AND ((counter_name)::text = 'FnfHit'::text))
    Total runtime: 4600.750 ms

MSSQL2008R2 Explain Analyze Result:

  |--Compute Scalar(DEFINE:([Expr1013]='fnf-item.aspx?pid='+[SlowTravel].[dbo].[Gourmet].[GourmetId] as [a].[GourmetId], [Expr1014]=isnull([Expr1012],(0)), [Expr1015]='1', [Expr1016]='gourmet.png', [Expr1017]=getdate(), [Expr1018]='NonOrderScheduler'))
       |--Compute Scalar(DEFINE:([Expr1012]=CASE WHEN [Expr1027]=(0) THEN NULL ELSE [Expr1028] END))
            |--Stream Aggregate(GROUP BY:([a].[GourmetId], [Expr1008], [Expr1009], [b].[PhotoId], [Expr1010], [Expr1011]) DEFINE:([Expr1027]=COUNT_BIG([SlowTravel].[dbo].[Counters].[TotalHit] as [c].[TotalHit]), [Expr1028]=SUM([SlowTravel].[dbo].[Counters].[TotalHit] as [c].[TotalHit]), [a].[GourmetNameC]=ANY([SlowTravel].[dbo].[Gourmet].[GourmetNameC] as [a].[GourmetNameC]), [a].[VendorUrl]=ANY([SlowTravel].[dbo].[Gourmet].[VendorUrl] as [a].[VendorUrl]), [a].[ForSearchOnly]=ANY([SlowTravel].[dbo].[Gourmet].[ForSearchOnly] as [a].[ForSearchOnly]), [a].[TelExt]=ANY([SlowTravel].[dbo].[Gourmet].[TelExt] as [a].[TelExt]), [a].[County]=ANY([SlowTravel].[dbo].[Gourmet].[County] as [a].[County]), [a].[Town]=ANY([SlowTravel].[dbo].[Gourmet].[Town] as [a].[Town]), [a].[AddressC]=ANY([SlowTravel].[dbo].[Gourmet].[AddressC] as [a].[AddressC])))
                 |--Nested Loops(Left Outer Join, OUTER REFERENCES:([a].[GourmetId], [Expr1026]) WITH UNORDERED PREFETCH)
                      |--Merge Join(Left Outer Join, MERGE:([a].[GourmetId])=([b].[FolderId]), RESIDUAL:([SlowTravel].[dbo].[Photos].[FolderId] as [b].[FolderId]=[SlowTravel].[dbo].[Gourmet].[GourmetId] as [a].[GourmetId]))
                      |    |--Sort(ORDER BY:([a].[GourmetId] ASC))
                      |    |    |--Compute Scalar(DEFINE:([Expr1008]=len([SlowTravel].[dbo].[Gourmet].[VendorUrl] as [a].[VendorUrl]), [Expr1009]='fnf-item.aspx?pid='+[SlowTravel].[dbo].[Gourmet].[GourmetId] as [a].[GourmetId], [Expr1010]=CONVERT(varchar(30),[SlowTravel].[dbo].[Gourmet].[CreatedDate] as [a].[CreatedDate],111), [Expr1011]=([SlowTravel].[dbo].[Gourmet].[AreaCode] as [a].[AreaCode]+'-')+[SlowTravel].[dbo].[Gourmet].[TelNo] as [a].[TelNo]))
                      |    |         |--Table Scan(OBJECT:([SlowTravel].[dbo].[Gourmet] AS [a]), WHERE:([SlowTravel].[dbo].[Gourmet].[Status] as [a].[Status]='A'))
                      |    |--Index Seek(OBJECT:([SlowTravel].[dbo].[Photos].[_dta_index_Photos_5_1925581898__K5_K2_K1] AS [b]), SEEK:([b].[MainPhoto]=(1)) ORDERED FORWARD)
                      |--Clustered Index Seek(OBJECT:([SlowTravel].[dbo].[Counters].[Counters_PK] AS [c]), SEEK:([c].[CounterName]='FnfHit' AND [c].[ItemId]=[SlowTravel].[dbo].[Gourmet].[GourmetId] as [a].[GourmetId]) ORDERED FORWARD)

How do I avoid sort in the explain result? Actuall I didn't ask any sort in the SQL Statement.
Thank you for your help.

  • 2
    Start by posting explain analyze of the SQL statement. Also, at minimum you should set effective_cache_size, a higher shared_buffers, and for most hardware lower random_page_cost (to say 2) to reflect real I/O performance. PostgreSQL's defaults are extremely conservative - it's designed to start up and run on tiny hardware if needed, but the downside is that it won't perform great untuned on decent hardware. – Craig Ringer Apr 18 '14 at 5:28
  • Dear Craig, Thank you for your suggestion. Just following your suggestion, adjust the above three parameters, the performance is not improved. It seems that I have no choice but to tune my SQL statement. But I am curious about why MSSQL2008R2 can have so good the performance without any adjustment on its parameters ? – wureka Apr 18 '14 at 8:41
  • 1
    Use explain analyze, not just explain. You've shown a pre-execution plan with no statistics about what actually happens when it gets run, what nodes took what time, how accurate the statistics estimates are, etc. Please edit again, comment here when done so I'm notified. (You should also run ANALYZE; beforehand, to update the table statistics). As for performance differences: Tends to vary case-by-case, with one DB lots faster at one thing, another DB lots faster at a different thing. All depends on planner/executor specifics. – Craig Ringer Apr 18 '14 at 8:43
  • Dear Craig, Thank you for your help. I have followed your suggestion. run "analyze; " and then "explain analyze MY SQL", and update the result to my content. – wureka Apr 18 '14 at 8:59
  • 1
    if possible try on Ubuntu 14.04 LTS , with lots of kernel optimisation you might just get better score. Redhat 7.0 rc is also reportedly giving good performance in general. – vedic Apr 23 '14 at 16:03
2

How do I avoid sort in the explain result? Actuall I didn't ask any sort in the SQL Statement.

You've asked for rows to be aggregated. One way to do this is to sort the data set and then scan it to collapse out duplicates. This can be faster than hash aggregation, which is the other way PostgreSQL knows how to do grouping.

So while you didn't explicitly say "sort the rows", it's still sorting them because of something you asked for.

The immediate problem is that PostgreSQL is being very conservative with how much memory it's using for sorts:

Sort Method: external merge Disk: 317656kB

and is doing a 300MB on-disk sort. You can see that pretty clearly if you take a look at the plain on explain.depesz.com.

If you:

SET work_mem = '400MB';

before running the query, it should be a whole lot different.

Unfortunately it's not a simple as changing your configuration, because PostgreSQL isn't too clever about resource management. Per the documentation on work_mem, it may use up to work_mem bytes per sort or join, per-session. So if you have max_connections = 50 and you're running complicated queries, you might find yourself using many gigabytes of working memory, exceeding free memory, and hitting swap. Which you really don't want.

It also seems to be doing a seqscan on counters, but since it's finding about 1/4 the rows match the condition, that's probably the right thing to do - an index scan would probably be slower.

I find the default work_mem way too conservative and tend to set it to at least 100MB on any reasonably large system. I also prefer to run PostgreSQL with PgBouncer in front, and a low max_connections, allowing me to throw more resources at each individual connection.

Frankly, I'm curious to see how MS SQL Server executes this, because the numbers you report are astonishing for a query like this.

|improve this answer|||||
  • I think MS SQL does some optimization regarding CHAR(n) vs VARCHAR(m) - see my answer. I wonder if Postgres can do this also? – mvp Apr 18 '14 at 9:30
3

In think your problem is that gourmet_id is declared as CHARACTER(30). If you change it to CHARACTER VARYING(30), it should work much faster.

This is what happens:

->  Seq Scan on counters c  (cost=0.00..250342.85 rows=2725633 width=16)
   (actual time=0.009..1610.743 rows=2751732 loops=1)
Filter: ((counter_name)::text = 'FnfHit'::text)
Rows Removed by Filter: 8919889

You would think that having primary key on (item_id, counter_name, target_date) in table counters would make this join condition to use your compound index:

LEFT JOIN Counters c ON c.ItemId=a.GourmetId AND c.CounterName='FnfHit'  

But, it does not! Why?

Because you defined gourmet_id as CHARACTER(30):

CREATE TABLE gourmet (
  ...
  gourmet_id CHARACTER(15) NOT NULL,
  ...

and joining field item_id was defined as CHARACTER VARYING(36):

CREATE TABLE counters (
  item_id CHARACTER VARYING(36) NOT NULL,
  counter_name CHARACTER VARYING(30) NOT NULL,

Technically, those fields cannot be effectively comparedand make use of compound index.

Fix is to declare gourmet_id as CHARACTER VARYING(30), not CHARACTER(30). This will permit to use your compound index and will speed it up.

|improve this answer|||||
  • yes. Following your suggestion, plus some other changes, the query speed does shrink to 4.6 seconds. However, I still wish the query speed could shrink to less than 1 second. Anyway, thank you for your help – wureka Apr 21 '14 at 22:08
1

Since the concatenation of groups creates te same groups as the groups themselves, the following query should be equivalent:

SELECT count(*) FROM (
      SELECT        xxx.gourmet_id AS item_id
                , xxx.gourmet_name_c AS item_name
                , xxx.vendor_url AS external_url
                , 'fnf-item.aspx?pid=' || xxx.gourmet_id AS fun_taiwan_url
                , xxx.photo_id
                , xxx.hit_count
                , to_char(xxx.created_date, 'YYYY/MM/DD HH24:MI:SS') AS created_date
                , '1' AS is_gourmet
                , xxx.for_search_only
                , xxx.area_code || '-' || xxx.tel_no AS tel_no
                , xxx.tel_ext
                , xxx.county
                , xxx.town
                , xxx.address_c
                , 'gourmet.png' AS icon_name
                , now() as updated_date
                , 'NonOrderScheduler' as updated_by
  FROM (
        SELECT        a.gourmet_id AS gourmet_id
                , a.gourmet_name_c AS gourmet_name_c
                , a.vendor_url AS vendor_url
                , b.photo_id AS photo_id
                , coalesce(SUM(c.total_hit),0) AS hit_count
                , a.created_date AS created_date
                , a.for_search_only AS for_search_only
                , a.area_code AS area_code
                , a.tel_no AS tel_no
                , a.tel_ext AS tel_ext
                , a.county AS county
                , a.town AS town
                , a.address_c AS address_c
        FROM      gourmet a
        LEFT JOIN photos b ON b.folder_id=a.gourmet_id AND b.main_photo='1'
        LEFT JOIN counters c ON c.item_id=a.gourmet_id AND c.counter_name='FnfHit'
        WHERE     a.status='A'
        GROUP BY  a.gourmet_id, a.gourmet_name_c, a.vendor_url, a.gourmet_id,b.photo_id
                , a.created_date,
                a.for_search_only, a.area_code , a.tel_no, a.tel_ext,a.county, a.town, a.address_c
        ) xxx
  GROUP BY  xxx.gourmet_id, xxx.gourmet_name_c
                , length(xxx.vendor_url),xxx.vendor_url
                , 'fnf-item.aspx?pid=' || xxx.gourmet_id,xxx.photo_id
                , xxx.hit_count
                , to_char(xxx.created_date,'YYYY/MM/DD HH24:MI:SS')
                , xxx.for_search_only
                , xxx.area_code || '-' || xxx.tel_no
                , xxx.tel_ext,xxx.county, xxx.town, xxx.address_c
) t
;

BTW: if you are only interested in the COUNT(*) , the query can be reduced to:

 SELECT count(*) FROM (
    SELECT        a.gourmet_id AS gourmet_id
            , a.gourmet_name_c AS gourmet_name_c
            , a.vendor_url AS vendor_url
            , b.photo_id AS photo_id
            , a.created_date AS created_date
            , a.for_search_only AS for_search_only
            , a.area_code AS area_code
            , a.tel_no AS tel_no
            , a.tel_ext AS tel_ext
            , a.county AS county
            , a.town AS town
            , a.address_c AS address_c
    FROM      gourmet a
    LEFT JOIN photos b ON b.folder_id=a.gourmet_id AND b.main_photo='1'
    WHERE     a.status='A'
    GROUP BY  a.gourmet_id, a.gourmet_name_c, a.vendor_url, a.gourmet_id,b.photo_id
            , a.created_date,
            a.for_search_only, a.area_code , a.tel_no, a.tel_ext,a.county, a.town, a.address_c
    ) xxx
    ;

But since all columns of a are dependent on the PK gourmet_id (and similar for table b), the query can further be reduced to:

SELECT count(*) FROM (
        SELECT        a.gourmet_id AS gourmet_id
                , b.photo_id AS photo_id
        FROM      gourmet a
        LEFT JOIN photos b ON b.folder_id=a.gourmet_id AND b.main_photo='1'
        WHERE     a.status='A'
        GROUP BY  a.gourmet_id,b.photo_id
        ) xxx
        ;

NOTE: the c table (counts) totally disappears, since it is a sum over a left join, so it only produces one subquery tuple.)

|improve this answer|||||
  • Hi joop, query the count is that I was trying to avoid the transmission time of the resultset. The actual SQL has no outside SQL count(*) FROM ..... – wureka Apr 21 '14 at 7:27
1

SQL server has some special optimizations for count(*) that postgres does not have so the test may be invalid. I would benchmark the time it takes for the full set from the application point of view. Another fair test would be the time it takes to insert into a scratch table (via INSERT /SELECT)

|improve this answer|||||
  • 1
    The fact that SQL server optimizes counts doesn't mean the test is invalid, just that other tests need to be run too. If you car's missing a wheel, that doesn't mean all other cars should be evaluated as if they're also missing a wheel. It means that it better be a damned good car to make up for only having 3 wheels. – Basic Aug 7 '14 at 15:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.