PostgreSQL 8.1 vs. SQLite 3.6.20 in the real application

For testing is used the production PostgreSQL database. I did build SQLite database as described here:
Trigger-based PostgreSQL to SQLite online replication

The test requests is real queries from the production system. The PostgreSQL is too slow on these queries and I search more performance database system for the intranet/internet portal. All tables have needed indices. Some tables are renamed in SQLite database but their names and fields names are similar.

Note
The PostgreSQL quries is rewrited for better index usage but SQLite query planner work fine without any additional tricks. The query conditions are generated by application query builder for user filters and settings and when you can see identical conditions these are not always identical.

Hardware


Intel(R) Core(TM)2 Quad CPU    Q6700  @ 2.66GHz
MemTotal:      8310900 kB
HDD: 1 VelociRaptor 10000 rpm


PostgreSQL parameters

work_mem = 100000                       # min 64, size in KB
maintenance_work_mem = 2000000          # min 1024, size in KB
shared_buffers = 200000                 # min 16 or max_connections*2, 8KB each


SQLite parameters

sqlite> pragma page_size;
4096
sqlite> pragma cache_size;
32000


PostgreSQL version


select version();
                                                    version
----------------------------------------------------------------------------------------------------------------
 PostgreSQL 8.1.15 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2 20061115 (prerelease) (Debian 4.1.1-21)
(1 row)


SQLite version

sqlite> select sqlite_version();
3.6.20


PostgreSQL DB size

SELECT sum(relpages*8192/1024/1024) as size_mb FROM pg_class WHERE NOT relname LIKE 'pg_%';
 size_mb
---------
    1248
(1 row)


SQLite DB size

$ ls -lh work.db
-rw-rw-rw- 1 postgres postgres 1.1G 2009-11-30 18:50 work.db


PostgreSQL: the archive search by number (runtime is 1356 ms)


SELECT  d.id,
d.phone_number          AS phone,
r.name                  AS region,
p.code                  AS point,
p.is_priority           AS point_is_priority,
c.code                  AS center_code,
a.value                 AS status,
u2.name                 AS user_5,
tp.name                 AS template,
c.name                  AS center,
(a.save_date at time zone interval '03:00')::date AS work_date,
date_trunc('second',(a.save_date at time zone interval '03:00')::time) AS work_time
FROM    offline.documents d
JOIN    auth.users u                  ON d.user_id = u.id
JOIN    auth.regions r                ON u.region_id = r.id
JOIN    auth.points p                 ON u.point_id = p.id
JOIN    auth.centers c                ON u.center_id = c.id
JOIN    offline.attributes a          ON d.id = a.document_id
AND     a.is_last
AND     a.value not IN ('Удален', 'Выполнен')
JOIN    offline.document_templates tp ON d.document_template_id = tp.id
JOIN    auth.users u2 on a.user_id=u2.id
WHERE   d.phone_number = 1234567890
--ORDER   BY work_date ASC, work_time ASC, status ASC;
ORDER   BY d.id;


explain analyze ...

"Sort  (cost=79749.02..79749.02 rows=2 width=213) (actual time=1355.607..1355.607 rows=1 loops=1)"
"  Sort Key: d.id"
"  ->  Merge Join  (cost=79730.29..79749.01 rows=2 width=213) (actual time=1355.594..1355.598 rows=1 loops=1)"
"        Merge Cond: ("outer".document_template_id = "inner".id)"
"        ->  Sort  (cost=79730.29..79730.30 rows=2 width=163) (actual time=1355.494..1355.495 rows=1 loops=1)"
"              Sort Key: d.document_template_id"
"              ->  Merge Join  (cost=79718.34..79730.28 rows=2 width=163) (actual time=1355.480..1355.483 rows=1 loops=1)"
"                    Merge Cond: ("outer".center_id = "inner".id)"
"                    ->  Sort  (cost=79718.34..79718.34 rows=2 width=125) (actual time=1355.268..1355.268 rows=1 loops=1)"
"                          Sort Key: u.center_id"
"                          ->  Merge Join  (cost=79465.87..79718.33 rows=2 width=125) (actual time=1355.260..1355.263 rows=1 loops=1)"
"                                Merge Cond: ("outer".user_id = "inner".id)"
"                                ->  Sort  (cost=79465.87..79465.88 rows=2 width=101) (actual time=1352.649..1352.649 rows=1 loops=1)"
"                                      Sort Key: a.user_id"
"                                      ->  Merge Join  (cost=355.26..79465.86 rows=2 width=101) (actual time=1352.637..1352.642 rows=1 loops=1)"
"                                            Merge Cond: ("outer".id = "inner".document_id)"
"                                            ->  Sort  (cost=355.26..355.27 rows=2 width=70) (actual time=4.237..4.238 rows=1 loops=1)"
"                                                  Sort Key: d.id"
"                                                  ->  Merge Join  (cost=268.02..355.25 rows=2 width=70) (actual time=4.229..4.231 rows=1 loops=1)"
"                                                        Merge Cond: ("outer".point_id = "inner".id)"
"                                                        ->  Sort  (cost=268.02..268.03 rows=2 width=61) (actual time=2.765..2.765 rows=1 loops=1)"
"                                                              Sort Key: u.point_id"
"                                                              ->  Merge Join  (cost=262.64..268.01 rows=2 width=61) (actual time=2.758..2.761 rows=1 loops=1)"
"                                                                    Merge Cond: ("outer".region_id = "inner".id)"
"                                                                    ->  Sort  (cost=262.64..262.64 rows=2 width=34) (actual time=2.749..2.749 rows=1 loops=1)"
"                                                                          Sort Key: u.region_id"
"                                                                          ->  Merge Join  (cost=10.03..262.63 rows=2 width=34) (actual time=2.742..2.745 rows=1 loops=1)"
"                                                                                Merge Cond: ("outer".user_id = "inner".id)"
"                                                                                ->  Sort  (cost=10.03..10.04 rows=2 width=26) (actual time=0.037..0.038 rows=1 loops=1)"
"                                                                                      Sort Key: d.user_id"
"                                                                                      ->  Index Scan using documents_phone_number_idx on documents d  (cost=0.00..10.02 rows=2 width=26) (actual time=0.027..0.028 rows=1 loops=1)"
"                                                                                            Index Cond: (phone_number = '1234567890'::text)"
"                                                                                ->  Index Scan using users_pkey on users u  (cost=0.00..248.75 rows=1807 width=16) (actual time=0.006..2.021 rows=1625 loops=1)"
"                                                                    ->  Index Scan using regions_pkey on regions r  (cost=0.00..5.33 rows=7 width=35) (actual time=0.005..0.007 rows=3 loops=1)"
"                                                        ->  Index Scan using points_pkey on points p  (cost=0.00..84.74 rows=984 width=17) (actual time=0.005..1.094 rows=895 loops=1)"
"                                            ->  Index Scan using attributes_document_id_idx on attributes a  (cost=0.00..78070.04 rows=416212 width=35) (actual time=1112.968..1347.803 rows=1327 loops=1)"
"                                                  Filter: (is_last AND (value <> 'Удален'::text) AND (value <> 'Выполнен'::text))"
"                                ->  Index Scan using users_pkey on users u2  (cost=0.00..248.75 rows=1807 width=32) (actual time=0.008..1.918 rows=1625 loops=1)"
"                    ->  Index Scan using center_pkey on centers c  (cost=0.00..11.52 rows=159 width=46) (actual time=0.006..0.149 rows=148 loops=1)"
"        ->  Index Scan using document_templates_pkey on document_templates tp  (cost=0.00..18.53 rows=51 width=58) (actual time=0.005..0.061 rows=51 loops=1)"
"Total runtime: 1355.782 ms"


SQLite: the archive search by number (runtime is 0 ms)


.timer on

SELECT  d.id,
d.phone_number          AS phone,
r.name                  AS region,
p.code                  AS point,
p.is_priority           AS point_is_priority,
c.code                  AS center_code,
a.value                 AS status,
u2.name                 AS user_5,
tp.name                 AS template,
c.name                  AS center,
datetime(a.save_date,'localtime') as save_date
FROM    document d
JOIN    user u                  ON d.user_id = u.id
JOIN    region r                ON u.region_id = r.id
JOIN    point p                 ON u.point_id = p.id
JOIN    center c                ON u.center_id = c.id
JOIN    document_status a          ON d.id = a.document_id
AND     a.is_last=1
AND     a.value not IN ('Удален', 'Выполнен')
JOIN    template tp ON d.template_id = tp.id
JOIN    user u2 on a.user_id=u2.id
WHERE   d.phone_number = 1234567890
--ORDER   BY work_date ASC, work_time ASC, status ASC;
ORDER   BY d.id;


The timer result

CPU Time: user 0.000000 sys 0.000000



explain query plan ...

0|0|TABLE document AS d WITH INDEX document_phone_number_idx ORDER BY
1|1|TABLE user AS u USING PRIMARY KEY
2|2|TABLE region AS r USING PRIMARY KEY
3|3|TABLE point AS p USING PRIMARY KEY
4|4|TABLE center AS c USING PRIMARY KEY
5|6|TABLE template AS tp USING PRIMARY KEY
6|5|TABLE document_status AS a WITH INDEX document_status_complex2_idx
7|7|TABLE user AS u2 USING PRIMARY KEY



PostgreSQL: the documents listing (runtime is 1742 ms)


SELECT  d.id            AS document_id,
        d.phone_number  AS phone,
        r.name          AS region,
        p.code          AS point,
        p.is_priority   AS point_is_priority,
        c.code          AS center_code,
        a.value         AS status,
        u2.name         AS last_user,
        tp.name         AS template,
        c.name          AS center,
        (a.save_date at time zone interval '03:00')::date                      AS work_date,
        date_trunc('second',(a.save_date at time zone interval '03:00')::time) AS work_time
    FROM    offline.documents d
    JOIN    auth.users u ON d.user_id = u.id
    JOIN    auth.regions r ON u.region_id = r.id
    JOIN    auth.points p ON u.point_id = p.id
    JOIN    auth.centers c ON u.center_id = c.id
    JOIN    offline.attributes a ON d.id = a.document_id
    AND     a.is_last
    AND     (a.value='' OR ''='')
--- AND     a.value!='Удален'
    JOIN    auth.users u2 on a.user_id=u2.id
    JOIN    offline.document_templates tp ON d.document_template_id = tp.id
    AND     (tp.id=NULL or ''='')
    WHERE   d.user_id IN (
                SELECT  id
                FROM    auth.users AS uu
                WHERE   group_id=5
                AND     (uu.region_id='5'::int  or '5'='')
                AND     (uu.region_id='5'::int     or '5'='')
                AND     (uu.center_id='1249'::int  or '1249'='')
                AND     (uu.center_id='1249'::int     or '1249'='')
                AND     (uu.point_id='1001684'::int    or '1001684'='')
                AND     (uu.point_id='1001684'::int       or '1001684'='')
            )
    AND     d.save_date BETWEEN ('2009-11-28'::timestamp) AND ('2009-11-29'::timestamp + interval '1 day')
    AND     true
    AND     (d.document_template_id=NULL or ''='')
ORDER   BY d.id ASC
OFFSET  '0'::int
LIMIT   '50'::int;


explain analyze ...

"Limit  (cost=69005.02..69005.02 rows=1 width=213) (actual time=1741.917..1741.954 rows=33 loops=1)"
"  ->  Sort  (cost=69005.02..69005.02 rows=1 width=213) (actual time=1741.915..1741.928 rows=33 loops=1)"
"        Sort Key: d.id"
"        ->  Hash Join  (cost=3780.16..69005.01 rows=1 width=213) (actual time=1732.782..1741.853 rows=33 loops=1)"
"              Hash Cond: ("outer".user_id = "inner".id)"
"              ->  Hash Join  (cost=3141.94..68360.72 rows=1209 width=131) (actual time=1726.624..1736.116 rows=1267 loops=1)"
"                    Hash Cond: ("outer".user_id = "inner".id)"
"                    ->  Merge Join  (cost=2888.68..68086.30 rows=1209 width=107) (actual time=1723.263..1731.276 rows=1267 loops=1)"
"                          Merge Cond: ("outer".id = "inner".document_id)"
"                          ->  Sort  (cost=2888.68..2891.71 rows=1213 width=76) (actual time=5.244..5.754 rows=1267 loops=1)"
"                                Sort Key: d.id"
"                                ->  Hash Join  (cost=30.94..2826.54 rows=1213 width=76) (actual time=1.534..3.882 rows=1267 loops=1)"
"                                      Hash Cond: ("outer".document_template_id = "inner".id)"
"                                      ->  Bitmap Heap Scan on documents d  (cost=12.28..2789.69 rows=1213 width=26) (actual time=1.412..2.380 rows=1267 loops=1)"
"                                            Recheck Cond: ((save_date >= '2009-11-28 00:00:00'::timestamp without time zone) AND (save_date <= '2009-11-30 00:00:00'::timestamp without time zone))"
"                                            ->  Bitmap Index Scan on documents_save_date_idx  (cost=0.00..12.28 rows=1213 width=0) (actual time=1.402..1.402 rows=1267 loops=1)"
"                                                  Index Cond: ((save_date >= '2009-11-28 00:00:00'::timestamp without time zone) AND (save_date <= '2009-11-30 00:00:00'::timestamp without time zone))"
"                                      ->  Hash  (cost=18.53..18.53 rows=51 width=58) (actual time=0.109..0.109 rows=51 loops=1)"
"                                            ->  Index Scan using document_templates_pkey on document_templates tp  (cost=0.00..18.53 rows=51 width=58) (actual time=0.007..0.069 rows=51 loops=1)"
"                          ->  Index Scan using attributes_document_id_idx on attributes a  (cost=0.00..63923.25 rows=502485 width=35) (actual time=0.011..1516.060 rows=502756 loops=1)"
"                                Filter: is_last"
"                    ->  Hash  (cost=248.75..248.75 rows=1807 width=32) (actual time=3.342..3.342 rows=1808 loops=1)"
"                          ->  Index Scan using users_pkey on users u2  (cost=0.00..248.75 rows=1807 width=32) (actual time=0.004..2.072 rows=1808 loops=1)"
"              ->  Hash  (cost=638.22..638.22 rows=1 width=94) (actual time=5.070..5.070 rows=1 loops=1)"
"                    ->  Merge Join  (cost=551.00..638.22 rows=1 width=94) (actual time=5.059..5.062 rows=1 loops=1)"
"                          Merge Cond: ("outer".point_id = "inner".id)"
"                          ->  Sort  (cost=551.00..551.01 rows=1 width=85) (actual time=4.001..4.001 rows=1 loops=1)"
"                                Sort Key: u.point_id"
"                                ->  Merge Join  (cost=539.06..550.99 rows=1 width=85) (actual time=3.990..3.993 rows=1 loops=1)"
"                                      Merge Cond: ("outer".center_id = "inner".id)"
"                                      ->  Sort  (cost=539.06..539.07 rows=1 width=47) (actual time=3.854..3.855 rows=1 loops=1)"
"                                            Sort Key: u.center_id"
"                                            ->  Merge Join  (cost=533.69..539.05 rows=1 width=47) (actual time=3.846..3.848 rows=1 loops=1)"
"                                                  Merge Cond: ("outer".region_id = "inner".id)"
"                                                  ->  Sort  (cost=533.69..533.69 rows=1 width=20) (actual time=3.834..3.835 rows=1 loops=1)"
"                                                        Sort Key: u.region_id"
"                                                        ->  Merge Join  (cost=280.39..533.68 rows=1 width=20) (actual time=3.827..3.829 rows=1 loops=1)"
"                                                              Merge Cond: ("outer".id = "inner".id)"
"                                                              ->  Index Scan using users_pkey on users u  (cost=0.00..248.75 rows=1807 width=16) (actual time=0.010..1.571 rows=1271 loops=1)"
"                                                              ->  Sort  (cost=280.39..280.40 rows=1 width=4) (actual time=1.718..1.718 rows=1 loops=1)"
"                                                                    Sort Key: uu.id"
"                                                                    ->  HashAggregate  (cost=280.37..280.38 rows=1 width=4) (actual time=1.712..1.712 rows=1 loops=1)"
"                                                                          ->  Index Scan using users_pkey on users uu  (cost=0.00..280.37 rows=1 width=4) (actual time=1.269..1.706 rows=1 loops=1)"
"                                                                                Filter: ((group_id = 5) AND (region_id = 5) AND (region_id = 5) AND (center_id = 1249) AND (center_id = 1249) AND (point_id = 1001684) AND (point_id = 1001684))"
"                                                  ->  Index Scan using regions_pkey on regions r  (cost=0.00..5.33 rows=7 width=35) (actual time=0.005..0.010 rows=6 loops=1)"
"                                      ->  Index Scan using center_pkey on centers c  (cost=0.00..11.52 rows=159 width=46) (actual time=0.005..0.103 rows=79 loops=1)"
"                          ->  Index Scan using points_pkey on points p  (cost=0.00..84.74 rows=984 width=17) (actual time=0.006..0.790 rows=623 loops=1)"
"Total runtime: 1742.239 ms"


SQLite: the documents listing (runtime is 12 ms)


.timer on

SELECT  d.id            AS document_id,
        d.phone_number  AS phone,
        r.name          AS region,
        p.code          AS point,
        p.is_priority   AS point_is_priority,
        c.code          AS center_code,
        a.value         AS status,
        u2.name         AS last_user,
        tp.name         AS template,
        c.name          AS center,
        datetime(a.save_date,'localtime') as save_date
    FROM    document d
    JOIN    user u ON d.user_id = u.id
    JOIN    region r ON u.region_id = r.id
    JOIN    point p ON u.point_id = p.id
    JOIN    center c ON u.center_id = c.id
    JOIN    document_status a ON d.id = a.document_id
    AND     a.is_last
    AND     (a.value='' OR ''='')
--- AND     a.value!='Удален'
    JOIN    user u2 on a.user_id=u2.id
    JOIN    template tp ON d.template_id = tp.id
    AND     (tp.id=NULL or ''='')
    WHERE   d.user_id IN (
                SELECT  id
                FROM    user AS uu
                WHERE   group_id=5
                AND     (uu.region_id=5  or 5='')
                AND     (uu.region_id=5  or 5='')
                AND     (uu.center_id=1249  or 1249='')
                AND     (uu.center_id=1249  or 1249='')
                AND     (uu.point_id=1001684 or 1001684='')
                AND     (uu.point_id=1001684 or 1001684='')
            )
    AND     d.save_date BETWEEN julianday('2009-11-28') AND julianday('2009-11-29','+1 day')
    AND     1=1
    AND     (d.template_id=NULL or ''='')
ORDER   BY d.id ASC
LIMIT   50
OFFSET  0;


The timer result is

CPU Time: user 0.004001 sys 0.008001


explain query plan ...

0|0|TABLE document AS d WITH INDEX document_user_id_idx
1|1|TABLE user AS u USING PRIMARY KEY
2|2|TABLE region AS r USING PRIMARY KEY
3|3|TABLE point AS p USING PRIMARY KEY
4|4|TABLE center AS c USING PRIMARY KEY
5|7|TABLE template AS tp USING PRIMARY KEY
6|5|TABLE document_status AS a WITH INDEX document_status_complex2_idx
7|6|TABLE user AS u2 USING PRIMARY KEY
0|0|TABLE user AS uu


PostgreSQL: the documents listing (runtime is 3430 ms) - simplified query


explain analyze
SELECT  d.id            AS document_id,
        d.phone_number  AS phone,
        r.name          AS region,
        p.code          AS point,
        p.is_priority   AS point_is_priority,
        c.code          AS center_code,
        a.value         AS status,
        u2.name         AS last_user,
        tp.name         AS template,
        c.name          AS center,
        (a.save_date at time zone interval '03:00')::date                      AS work_date,
        date_trunc('second',(a.save_date at time zone interval '03:00')::time) AS work_time
    FROM    offline.documents d
    JOIN    auth.users u ON d.user_id = u.id
    JOIN    auth.regions r ON u.region_id = r.id
    JOIN    auth.points p ON u.point_id = p.id
    JOIN    auth.centers c ON u.center_id = c.id
    JOIN    offline.attributes a ON d.id = a.document_id
    AND     a.is_last
    AND     (a.value='' OR ''='')
--- AND     a.value!='Удален'
    JOIN    auth.users u2 on a.user_id=u2.id
    JOIN    offline.document_templates tp ON d.document_template_id = tp.id
    AND     (tp.id=NULL or ''='')
    WHERE   1=1
    AND  u.group_id=5
    AND  (u.region_id='5'::int  or '5'='')
    AND  (u.region_id='5'::int     or '5'='')
    AND  (u.center_id='1249'::int  or '1249'='')
    AND  (u.center_id='1249'::int     or '1249'='')
    AND  (u.point_id='1001684'::int    or '1001684'='')
    AND  (u.point_id='1001684'::int       or '1001684'='')
    AND  d.save_date BETWEEN ('2009-11-28'::timestamp) AND ('2009-11-29'::timestamp + interval '1 day')
    AND  true
    AND  (d.document_template_id=NULL or ''='')
ORDER   BY d.id ASC
OFFSET  '0'::int
LIMIT   '50'::int;


explain analyze ...

"Limit  (cost=300123674.36..300126187.10 rows=1 width=213) (actual time=3396.515..3397.608 rows=33 loops=1)"
"  ->  Merge Join  (cost=300123674.36..300126187.10 rows=1 width=213) (actual time=3396.514..3397.585 rows=33 loops=1)"
"        Merge Cond: ("outer".document_id = "inner".id)"
"        ->  Sort  (cost=120565.48..121821.83 rows=502542 width=59) (actual time=2964.899..3162.001 rows=502710 loops=1)"
"              Sort Key: a.document_id"
"              ->  Hash Join  (cost=253.27..72977.55 rows=502542 width=59) (actual time=3.427..2068.470 rows=503382 loops=1)"
"                    Hash Cond: ("outer".user_id = "inner".id)"
"                    ->  Index Scan using attributes_document_id_idx on attributes a  (cost=0.00..63929.80 rows=502542 width=35) (actual time=0.011..1482.646 rows=503426 loops=1)"
"                          Filter: is_last"
"                    ->  Hash  (cost=248.75..248.75 rows=1807 width=32) (actual time=3.405..3.405 rows=1808 loops=1)"
"                          ->  Index Scan using users_pkey on users u2  (cost=0.00..248.75 rows=1807 width=32) (actual time=0.007..2.140 rows=1808 loops=1)"
"        ->  Sort  (cost=300003108.88..300003108.88 rows=1 width=158) (actual time=4.889..4.902 rows=33 loops=1)"
"              Sort Key: d.id"
"              ->  Merge Join  (cost=300003090.19..300003108.87 rows=1 width=158) (actual time=4.660..4.814 rows=33 loops=1)"
"                    Merge Cond: ("outer".document_template_id = "inner".id)"
"                    ->  Sort  (cost=300003090.19..300003090.20 rows=1 width=108) (actual time=4.634..4.643 rows=33 loops=1)"
"                          Sort Key: d.document_template_id"
"                          ->  Hash Join  (cost=300000306.70..300003090.18 rows=1 width=108) (actual time=3.468..4.566 rows=33 loops=1)"
"                                Hash Cond: ("outer".user_id = "inner".id)"
"                                ->  Bitmap Heap Scan on documents d  (cost=12.28..2789.69 rows=1213 width=26) (actual time=1.414..2.334 rows=1267 loops=1)"
"                                      Recheck Cond: ((save_date >= '2009-11-28 00:00:00'::timestamp without time zone) AND (save_date <= '2009-11-30 00:00:00'::timestamp without time zone))"
"                                      ->  Bitmap Index Scan on documents_save_date_idx  (cost=0.00..12.28 rows=1213 width=0) (actual time=1.403..1.403 rows=1267 loops=1)"
"                                            Index Cond: ((save_date >= '2009-11-28 00:00:00'::timestamp without time zone) AND (save_date <= '2009-11-30 00:00:00'::timestamp without time zone))"
"                                ->  Hash  (cost=300000294.42..300000294.42 rows=1 width=90) (actual time=1.684..1.684 rows=1 loops=1)"
"                                      ->  Nested Loop  (cost=300000000.00..300000294.42 rows=1 width=90) (actual time=1.251..1.680 rows=1 loops=1)"
"                                            ->  Index Scan using users_pkey on users u  (cost=0.00..280.37 rows=1 width=16) (actual time=1.221..1.644 rows=1 loops=1)"
"                                                  Filter: ((group_id = 5) AND (region_id = 5) AND (region_id = 5) AND (center_id = 1249) AND (center_id = 1249) AND (point_id = 1001684) AND (point_id = 1001684))"
"                                            ->  Nested Loop  (cost=200000000.00..200000014.03 rows=1 width=98) (actual time=0.025..0.029 rows=1 loops=1)"
"                                                  ->  Nested Loop  (cost=100000000.00..100000009.40 rows=1 width=81) (actual time=0.014..0.016 rows=1 loops=1)"
"                                                        ->  Index Scan using regions_pkey on regions r  (cost=0.00..4.26 rows=1 width=35) (actual time=0.006..0.007 rows=1 loops=1)"
"                                                              Index Cond: (5 = id)"
"                                                        ->  Index Scan using center_pkey on centers c  (cost=0.00..5.13 rows=1 width=46) (actual time=0.006..0.007 rows=1 loops=1)"
"                                                              Index Cond: (1249 = id)"
"                                                  ->  Index Scan using points_pkey on points p  (cost=0.00..4.62 rows=1 width=17) (actual time=0.008..0.009 rows=1 loops=1)"
"                                                        Index Cond: (1001684 = id)"
"                    ->  Index Scan using document_templates_pkey on document_templates tp  (cost=0.00..18.53 rows=51 width=58) (actual time=0.005..0.086 rows=78 loops=1)"
"Total runtime: 3429.668 ms"


SQLite: the documents listing (runtime is 12 ms) - simplified query


.timer on

SELECT  d.id            AS document_id,
        d.phone_number  AS phone,
        r.name          AS region,
        p.code          AS point,
        p.is_priority   AS point_is_priority,
        c.code          AS center_code,
        a.value         AS status,
        u2.name         AS last_user,
        tp.name         AS template,
        c.name          AS center,
        datetime(a.save_date,'localtime') as save_date
    FROM    document d
    JOIN    user u ON d.user_id = u.id
    JOIN    region r ON u.region_id = r.id
    JOIN    point p ON u.point_id = p.id
    JOIN    center c ON u.center_id = c.id
    JOIN    document_status a ON d.id = a.document_id
    AND     a.is_last
    AND     (a.value='' OR ''='')
--- AND     a.value!='Удален'
    JOIN    user u2 on a.user_id=u2.id
    JOIN    template tp ON d.template_id = tp.id
    AND     (tp.id=NULL or ''='')
    WHERE 1=1
     AND u.group_id=5
     AND (u.region_id=5  or 5='')
    AND (u.region_id=5     or 5='')
    AND (u.center_id=1249  or 1249='')
    AND (u.center_id=1249  or 1249='')
    AND (u.point_id=1001684 or 1001684='')
    AND (u.point_id=1001684 or 1001684='')
    AND     d.save_date BETWEEN julianday('2009-11-28') AND julianday('2009-11-29','+1 day')
    AND     1=1
    AND     (d.template_id=NULL or ''='')
ORDER   BY d.id ASC
LIMIT   50
OFFSET  0;


The timer result is

CPU Time: user 0.004000 sys 0.008000


explain query plan ...

0|0|TABLE document AS d WITH INDEX document_save_date_idx
1|1|TABLE user AS u USING PRIMARY KEY
2|2|TABLE region AS r USING PRIMARY KEY
3|3|TABLE point AS p USING PRIMARY KEY
4|4|TABLE center AS c USING PRIMARY KEY
5|7|TABLE template AS tp USING PRIMARY KEY
6|5|TABLE document_status AS a WITH INDEX document_status_complex2_idx
7|6|TABLE user AS u2 USING PRIMARY KEY


Upd.
На форуме Dmitry Arefiev задал вопросы по трактовке полученных измерений. Привожу здесь сами вопросы и свои ответы:

- среда исполнения запросов;
- сколько записей каждый запрос должен возвратить;
- что за время мерилось;
- что там со статистикой;
- расположены файлы БД на одном носителе или на разных;
- кэш файловой системы / СУБД холодный или горячий;


1. Указано ."timer on", а с точки начинаются команды эскулайт шелла. Ни в каких врапперах этих команд нет, поскольку имплементированы они именно в коде шелла эскулайт.

2. Приведен explain analyze в постгресе, в котором кол-во возвращаемых записей подсчитывается:


"... (actual time=1355.607..1355.607 rows=1 loops=1)"
...
"... (actual time=1741.917..1741.954 rows=33 loops=1)"


Т.е. первый запрос вернет 1 строку, а второй - 33.

3. Сказано, что измерялось время встроенным таймером в эскулайт и командой explain analyze в постгресе. Смотрим "man time"
user - время CPU, которое занял пользователь (сумма значений tms_utime и
tms_cutime в структуре struct tms , которая возвращается вызовом times(2)),
sys - время CPU занятое системой (сумма значений tms_stime и tms_cstime в структуре struct tms ,
которая возвращается вызовом times(2)).
В документации постгреса сказано, что explain analyze вернет реальное время, опять же, обратимся к man time
real - реальное время выполнение между вызовом и завершением

Постгрес не считает время на передачу данных клиенту, т.к. это время не может быть посчитано самой СУБД. Показывается время извлечения данных, притом нужно учесть, что для большого кол-ва записей может потребоваться еще дополнительно значительное время на их передачу клиенту. Эскулайт исполняется в процессе приложения, потому извлеченные данные сразу же доступны клиенту, не требуется их куда-либо передавать.

Поясню на примере:

time sqlite3 work.db '.read query'
real    0m0.014s
user    0m0.012s
sys     0m0.004s

Здесь 14 мс - время на выборку, передачу данных клиенту и вывод их на консоль. Процессор многоядерный, потому real < user+sys.

4. Постгрес о наличи своей статистики отрапортовал. Притом, поскольку реальный результат совпал с ожидаемым (см. explain analyze), известно, что статистика актуальная. В эскулайте без разницы, была собрана статистика командой analyze или нет, потому и не упоминается.

5. В "Hardware" указан 1 диск, на нем все и расположено. А где же еще. Есть еще системный диск и проч., но в тестах они не участвуют, потому и не упоминаются.

6. Для эскулайт время sys как раз показывает заполнение кэша ФС, если кэш "горячий", то это время равно 0. В постгресе кэш горячий, т.к. сказано, что это _рабочая_ система.

Comments

Post a Comment

Popular posts from this blog

Открытый софт для научных расчетов

Задачки у меня бывают разные, кратенькое описание того, что может потребоваться я приводил здесь: Софт для спектрального анализа/численного моделирования/etc. Дело происходит в Linux Debian, соответственно, интересует софт, который устанавливается из стандартного репозитория (что подразумевает опенсорсность).  Для начала я протестировал двумерное Фурье преобразование (прямое и обратное) на широко известном тестовом изображении Lenna. Заодно в процессе поиска нужных функций увидел заметки об истории этого изображения  Просто Лена  и  Обработка изображений: кто такая Лена Выбор софта для такой типичной задачки вполне себе наличествует: Scientific Computing with Free software on GNU/Linux HOWTO Пакеты прикладных программ Инструменты визуализации данных для Linux На практике же оказалось не так радужно, как хотелось бы. Из того, что есть в дебиане, работает "из коробки" только пакет octave . Зато для него можно установить множество расширений - нап
Read more

Счетчики в SQLite

Недавно ко мне обратился прикладной программист, работающий с моим фрэймворком для AOLServer и SQLite с вопросом, как ему сделать счетчик записей, возвращенных запросом. Разумеется, можно создать временную таблицу или забиндить тиклевскую функцию, но есть и более простой способ - использовать написанный мною модуль key или tempkey (отличаются только тем, что последний использует временную таблицу, таким образом, при хранении временных объектов в ОЗУ не требуется запись на диск). Модуль key создает при инициализации таблицу keys (а модуль tempkey - временную таблицу tempkeys), в которой и будут храниться все ключи; таким образом, можно использовать практически неограниченное количество ключей без ущерба для производительности и не нуждаясь в большом количестве ОЗУ. Рассмотрим на примере неименованного временного счетчика: create table colors(name text); insert into colors values ('Red'); insert into colors values ('Green'); insert into colors values ('Blue'); sel
Read more

Кольцевые структуры в геофизике

Опубликовал теоретическое обоснование и результаты численной модели для появления кольцевых структур в гравитационном поле. Поскольку найденные структуры существуют после применения фильтра высоких частот к полю, из этого следуют разные интересные вещи. Например, эти же структуры проявляются в цифровой модели рельефа и на космических снимках. Таким образом, подтверждаются практические работы Ю.И. Фивенского о существовании разномасштабных кольцевых структур, их видимости на рельефе и на снимках и пропорциональности их радиусов с глубиной залегания вызывающих их источников таких, как ловушки углеводородов, с коэффициентом около 0.7. Также можно предположить, что снежный покров работает как фильтр высокой частоты, что и позволяет лучше проявиться паттернам на поверхности - для тех конфигураций источников, которые иначе без фильтрации гравитационного поля не различимы. Ссылка на гитхаб-репозиторий со статьями: https://github.com/mobigroup/articles/  Первые две работы вводные, интересны
Read more