|
There was some doubt as for the speed of doing the select count(*) in
PostgreSQL and Oracle.
To that end, I copied the most part of the Oracle table I used before to Postgres. Although the copy wasn't complete, the resulting table is already significantly larger than the table it was copied from. The result still shows that Oracle is significantly faster: Oracle result: SQL> alter system flush buffer_cache; System altered. SQL> select /*+ full(NO) noparallel */ count(*) from ni_occurrence no; COUNT(*) ---------- 402062638 Elapsed: 00:03:16.45 Hints are necessary because Oracle table is declared as parallel and I didn't want the PK index to be used for counting. Oracle has a good habit of using PK's for counting, if available. SQL> select bytes/1048576 as MB 2 from user_segments 3 where segment_name='NI_OCCURRENCE'; MB ---------- 35329 Elapsed: 00:00:00.85 SQL> So, oracle stores 402 million records in 35GB and counts them in 3 minutes 16.45 seconds The very same table was partially copied to Postgres, copying died with ORA-01555 snapshot too old sometimes this morning. I ran vacuumdb -f -z on the database after the copy completed and the results are below. mgogala=# select count(*) from ni_occurrence; count ----------- 382400476 (1 row) Time: 221716.466 ms mgogala=# mgogala=# select 221/60::real; ?column? ------------------ 3.68333333333333 (1 row) Time: 0.357 ms mgogala=# mgogala=# select pg_size_pretty(pg_table_size('ni_occurrence')); pg_size_pretty ---------------- 46 GB (1 row) Time: 0.420 ms mgogala=# The database wasn't restarted, no caches were flushed, the comparison was done with a serious advantage for PostgreSQL. Postgres needed 3.68 minutes to complete the count which is about the same Oracle but still somewhat slower. Also, I am worried about the sizes. Postgres table is 11GB larger than the original, despite having less data. That was an unfair and unbalanced comparison because Oracle's cache was flushed and Oracle was artificially restrained to use the full table scan without the aid of parallelism. Here is the same result, with no hints and the autotrace on, which shows what happens if I turn the hints off: SQL> select count(*) from ni_occurrence no; COUNT(*) ---------- 402062638 Elapsed: 00:00:52.61 Execution Plan ---------------------------------------------------------- Plan hash value: 53476935 -------------------------------------------------------------------------------- ---------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT|
PQ Distrib |
-------------------------------------------------------------------------------- ---------------------------------------- | 0 | SELECT STATEMENT | | 1 | 54001 (19)| 00:01:08 | | | | | 1 | SORT AGGREGATE | | 1 | | | | | | | 2 | PX COORDINATOR | | | | | | | | | 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) | | 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | | | 5 | PX BLOCK ITERATOR | | 402M| 54001 (19)| 00:01:08 | Q1,00 | PCWC | | | 6 | INDEX FAST FULL SCAN| IDX_NI_OCCURRENCE_PID | 402M| 54001 (19)| 00:01:08 | Q1,00 | PCWP | | -------------------------------------------------------------------------------- ---------------------------------------- It took just 52 seconds to count everything, but Oracle didn't even scan the table, it scanned a unique index, in parallel. That is the algorithmic advantage that forced me to restrict the execution plan with hints. My conclusion is that the speed of the full scan is OK, about the same as Oracle speed. There are, however, three significant algorithm advantages on the Oracle's side: 1) Oracle can use indexes to calculate "select count" 2) Oracle can use parallelism. 3) Oracle can use indexes in combination with the parallel processing. Here are the descriptions: SQL> desc ni_occurrence Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL NUMBER(22) PERMANENT_ID NOT NULL VARCHAR2(12) CALL_LETTERS NOT NULL VARCHAR2(5) AIRDATE NOT NULL DATE DURATION NOT NULL NUMBER(4) PROGRAM_TITLE VARCHAR2(360) COST NUMBER(15) ASSETID NUMBER(12) MARKET_ID NUMBER GMT_TIME DATE ORIG_ST_OCC_ID NUMBER EPISODE VARCHAR2(450) IMPRESSIONS NUMBER SQL> mgogala=# \d ni_occurrence Table "public.ni_occurrence" Column | Type | Modifiers ----------------+-----------------------------+----------- id | bigint | not null permanent_id | character varying(12) | not null call_letters | character varying(5) | not null airdate | timestamp without time zone | not null duration | smallint | not null program_title | character varying(360) | cost | bigint | assetid | bigint | market_id | bigint | gmt_time | timestamp without time zone | orig_st_occ_id | bigint | episode | character varying(450) | impressions | bigint | Indexes: "ni_occurrence_pk" PRIMARY KEY, btree (id) mgogala=# Oracle block is 16k, version is 10.2.0.5 RAC, 64 bit (is anybody still using 32bit db servers?) . Postgres is 9.0.1, 64 bit. Both machines are running Red Hat 5.5: [mgogala@lpo-postgres-d01 ~]$ cat /etc/redhat-release Red Hat Enterprise Linux Server release 5.5 (Tikanga) [mgogala@lpo-postgres-d01 ~]$ Linux lpo-postgres-d01 2.6.18-194.el5 #1 SMP Tue Mar 16 21:52:39 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux [mgogala@lpo-postgres-d01 ~]$ -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 www.vmsinfo.com The Leader in integrated Media Intelligence Solutions |
