Re: Slow count(*) again...
От | Mladen Gogala |
---|---|
Тема | Re: Slow count(*) again... |
Дата | |
Msg-id | 4CB4542E.5010406@vmsinfo.com обсуждение исходный текст |
Ответ на | Re: Slow count(*) again... (david@lang.hm) |
Ответы |
Re: Slow count(*) again...
(Jon Nelson <jnelson+pgsql@jamponi.net>)
Re: Slow count(*) again... (Tom Lane <tgl@sss.pgh.pa.us>) Re: Slow count(*) again... (david@lang.hm) |
Список | pgsql-performance |
david@lang.hm wrote: > from a PR point of view, speeding up the trivil count(*) case could be > worth it, just to avoid people complaining about it not being fast. > > Fixing PR stuff is not the approach that I would take. People are complaining about select count(*) because they're using it in all the wrong places. My assessment that there is a problem with sequential scan was wrong. Now, let's again take Oracle as the measure. Someone asked me about caching the data. Here it is: SQL> connect system/********* Connected. SQL> alter system flush buffer_cache; System altered. Elapsed: 00:00:12.68 SQL> connect adbase/********* Connected. SQL> alter session set db_file_multiblock_read_Count=128; Session altered. Elapsed: 00:00:00.41 SQL> select count(*) from ni_occurrence; COUNT(*) ---------- 402062638 Elapsed: 00:02:37.77 SQL> select bytes/1048576 MB from user_segments 2 where segment_name='NI_OCCURRENCE'; MB ---------- 35329 Elapsed: 00:00:00.20 SQL> So, the results weren't cached the first time around. The explanation is the fact that Oracle, as of the version 10.2.0, reads the table in the private process memory, not in the shared buffers. This table alone is 35GB in size, Oracle took 2 minutes 47 seconds to read it using the full table scan. If I do the same thing with PostgreSQL and a comparable table, Postgres is, in fact, faster: psql (9.0.1) Type "help" for help. news=> \timing Timing is on. news=> select count(*) from moreover_documents_y2010m09; count ---------- 17242655 (1 row) Time: 113135.114 ms news=> select pg_size_pretty(pg_table_size('moreover_documents_y2010m09')); pg_size_pretty ---------------- 27 GB (1 row) Time: 100.849 ms news=> The number of rows is significantly smaller, but the table contains rather significant "text" field which consumes quite a bit of TOAST storage and the sizes are comparable. Postgres read through 27GB in 113 seconds, less than 2 minutes and oracle took 2 minutes 37 seconds to read through 35GB. I stand corrected: there is nothing wrong with the speed of the Postgres sequential scan. -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 www.vmsinfo.com
В списке pgsql-performance по дате отправления: