Re: [HACKERS] What can we learn from MySQL?
От | Bruce Momjian |
---|---|
Тема | Re: [HACKERS] What can we learn from MySQL? |
Дата | |
Msg-id | 200404252041.i3PKfRJ28435@candle.pha.pa.us обсуждение исходный текст |
Ответ на | Re: [HACKERS] What can we learn from MySQL? (Peter Eisentraut <peter_e@gmx.net>) |
Ответы |
Re: [HACKERS] What can we learn from MySQL?
(Rob <pgadmin@itsbeen.sent.com>)
|
Список | pgsql-advocacy |
Peter Eisentraut wrote: > Rob wrote: > > But I think there is room to go further, I don't see any reason why > > that default install can't include example DBs, > > One reason is that a useful example database would likely have a > download footprint of 10 MB or more. Having this in the default > download would not be appreciated by many people. Of course having > some example database available at all would be a good idea, but then > as a separate download. Here is a little psql script I wrote to populate a table with random data. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 \set ECHO all \timing DROP TABLE perftest; CREATE TABLE perftest (col text); -- prime table with one row INSERT INTO perftest VALUES ('0.364461265208414'); -- continously double the table size INSERT INTO perftest SELECT random()::text FROM perftest; INSERT INTO perftest SELECT random()::text FROM perftest; INSERT INTO perftest SELECT random()::text FROM perftest; INSERT INTO perftest SELECT random()::text FROM perftest; INSERT INTO perftest SELECT random()::text FROM perftest; INSERT INTO perftest SELECT random()::text FROM perftest; INSERT INTO perftest SELECT random()::text FROM perftest; INSERT INTO perftest SELECT random()::text FROM perftest; INSERT INTO perftest SELECT random()::text FROM perftest; INSERT INTO perftest SELECT random()::text FROM perftest; INSERT INTO perftest SELECT random()::text FROM perftest; INSERT INTO perftest SELECT random()::text FROM perftest; INSERT INTO perftest SELECT random()::text FROM perftest; INSERT INTO perftest SELECT random()::text FROM perftest; -- insert a constant in the middle of the table, for use later INSERT INTO perftest VALUES ('0.608254158221304'); INSERT INTO perftest SELECT random()::text FROM perftest; -- 32770 rows -- vacuum, create index VACUUM ANALYZE perftest; CREATE INDEX i_perftest ON perftest (col); -- reduce chance of checkpoint during tests CHECKPOINT; -- turn on logging SET log_duration = TRUE; SET client_min_messages = 'log'; -- run normal and prepared queries SELECT col FROM perftest WHERE col = '0.608254158221304'; SELECT col FROM perftest WHERE col = '0.608254158221304'; SELECT col FROM perftest WHERE col = '0.608254158221304'; SELECT col FROM perftest WHERE col = '0.608254158221304'; SELECT col FROM perftest WHERE col = '0.608254158221304'; PREPARE perftest_prep (text) AS SELECT col FROM perftest WHERE col = $1; EXECUTE perftest_prep ('0.608254158221304'); EXECUTE perftest_prep ('0.608254158221304'); EXECUTE perftest_prep ('0.608254158221304'); EXECUTE perftest_prep ('0.608254158221304'); EXECUTE perftest_prep ('0.608254158221304'); -- first time the entire statement SET log_statement_stats = TRUE; -- run normal and prepared queries SELECT col FROM perftest WHERE col = '0.608254158221304'; SELECT col FROM perftest WHERE col = '0.608254158221304'; SELECT col FROM perftest WHERE col = '0.608254158221304'; SELECT col FROM perftest WHERE col = '0.608254158221304'; SELECT col FROM perftest WHERE col = '0.608254158221304'; PREPARE perftest_prep (text) AS SELECT col FROM perftest WHERE col = $1; EXECUTE perftest_prep ('0.608254158221304'); EXECUTE perftest_prep ('0.608254158221304'); EXECUTE perftest_prep ('0.608254158221304'); EXECUTE perftest_prep ('0.608254158221304'); EXECUTE perftest_prep ('0.608254158221304'); -- now log each query stage SET log_statement_stats = FALSE; SET log_parser_stats = TRUE; SET log_planner_stats = TRUE; SET log_executor_stats = TRUE; -- run normal and prepared queries SELECT col FROM perftest WHERE col = '0.608254158221304'; SELECT col FROM perftest WHERE col = '0.608254158221304'; SELECT col FROM perftest WHERE col = '0.608254158221304'; SELECT col FROM perftest WHERE col = '0.608254158221304'; SELECT col FROM perftest WHERE col = '0.608254158221304'; EXECUTE perftest_prep ('0.608254158221304'); EXECUTE perftest_prep ('0.608254158221304'); EXECUTE perftest_prep ('0.608254158221304'); EXECUTE perftest_prep ('0.608254158221304'); EXECUTE perftest_prep ('0.608254158221304');
В списке pgsql-advocacy по дате отправления: