понедельник, 30 ноября 2009 г.

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. В постгресе кэш горячий, т.к. сказано, что это _рабочая_ система.

Комментариев нет:


(C) Alexey Pechnikov aka MBG, mobigroup.ru