Message-ID: <4F8375E3.1030409@fuzzy.cz>
Date: Tue, 10 Apr 2012 01:50:59 +0200
From: Tomas Vondra <tv@fuzzy.cz>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:11.0) Gecko/20120312 Thunderbird/11.0
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: about multiprocessingmassdata
References: <2012040423524938620013@gmail.com> <4F7C7827.7080705@fuzzy.cz>
	<2012040500491136389424@gmail.com> <4F7C7E07.1050507@fuzzy.cz>
	<201204051028153413205@gmail.com> <4F7D8F28.6000805@fuzzy.cz>
	<201204052144226175873@gmail.com> <4F7DB092.1000400@fuzzy.cz>
	<CAHyXU0zbxgrpSfBumRd-V4GvsXXM1Xh1WC-LTcjBY4c_S9PDmQ@mail.gmail.com>
In-Reply-To: 
 <CAHyXU0zbxgrpSfBumRd-V4GvsXXM1Xh1WC-LTcjBY4c_S9PDmQ@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

On 10.4.2012 00:37, Merlin Moncure wrote:
> On Thu, Apr 5, 2012 at 9:47 AM, Tomas Vondra <tv@fuzzy.cz> wrote:
>> If you have slower drives, the dependency is about linear (half the
>> speed -> twice the time). So either your drives are very slow, or
>> there's something rotten.
>>
>> I still haven's seen iostat / vmstat output ... that'd tell us much more
>> about the causes.
> 
> geometry column can potentially quite wide.  one thing we need to see
> is the table has any indexes -- in particular gist/gin on the
> geometry.

Yeah, but in one of the previous posts the OP posted this:

relname      | relpages  |  reltuples
-------------+----------+-------------
poi_all_new |  2421133 | 6.53328e+06

which means the table has ~ 19GB for 6.5 million rows, so it's like
2.8GB per 1 million of rows, i.e. ~3kB per row. I've been working with 1
million rows and 1.3GB of data, so it's like 50% of the expected amount.

But this does not explain why the SELECT COUNT(*) takes 364 seconds on
that machine. That'd mean ~8MB/s.

Regarding the indexes, the the OP already posted a description of the
table and apparently there are these indexes:

Indexes:
    "poi_all_new_pk" PRIMARY KEY, btree (ogc_fid)
    "poi_all_new_flname_idx" btree (flname)
    "poi_all_new_geom_idx" btree (wkb_geometry)
    "poi_all_new_ogc_fid_idx" btree (ogc_fid)
    "poi_all_new_pinyin_idx" btree (pinyin)

So none of them is GIN/GIST although some one of them is on the geometry
column.

T.