Re: query a table with lots of coulmns

Поиск
Список
Период
Сортировка
От Björn Wittich
Тема Re: query a table with lots of coulmns
Дата
Msg-id 541D6A28.5050803@gmx.de
обсуждение исходный текст
Ответ на Re: query a table with lots of coulmns  (Marc Mamin <M.Mamin@intershop.de>)
Список pgsql-performance
Hi,

ok here are my schemata : cachetable : 30 - 50 Mio rows, worktable 5 Mio
- 25 Mio


CREATE TABLE cachetable
(
   a text,
   b text NOT NULL,
   c text,
   d text,
   e timestamp without time zone DEFAULT now(),
   f text,
   s1 integer DEFAULT 0,
   s2 integer DEFAULT 0,
   s3 integer DEFAULT 0,
   s4 integer DEFAULT 0,
   s5 integer DEFAULT 0,
   s6 integer DEFAULT 0,
   s7 integer DEFAULT 0,
   s8 integer DEFAULT 0,
   s9 integer DEFAULT 0,
   s10 integer DEFAULT 0,
   s11 integer DEFAULT 0,
   s12 integer DEFAULT 0,
   s13 integer DEFAULT 0,
   s14 integer DEFAULT 0,
   s15 integer DEFAULT 0,
   s16 integer DEFAULT 0,
   s17 integer DEFAULT 0,
   s18 integer DEFAULT 0,
   s19 integer DEFAULT 0,
   s20 integer DEFAULT 0,
   s21 integer DEFAULT 0,
   s22 integer DEFAULT 0,
   s23 integer DEFAULT 0,
   s24 integer DEFAULT 0,
   s25 integer DEFAULT 0,
   s26 integer DEFAULT 0,
   s27 integer DEFAULT 0,
   s28 integer DEFAULT 0,
   s29 integer DEFAULT 0,
   s30 integer DEFAULT 0,
   s31 integer DEFAULT 0,
   s32 integer DEFAULT 0,
   s33 integer DEFAULT 0,
   s34 integer DEFAULT 0,
   s35 integer DEFAULT 0,
   s36 integer DEFAULT 0,
   s37 integer DEFAULT 0,
   s38 integer DEFAULT 0,
   s39 integer DEFAULT 0,
   s40 integer DEFAULT 0,
   s41 integer DEFAULT 0,
   s42 integer DEFAULT 0,
   s43 integer DEFAULT 0,
   s44 integer DEFAULT 0,
   s45 integer DEFAULT 0,
   s46 integer DEFAULT 0,
   s47 integer DEFAULT 0,
   s48 integer DEFAULT 0,
   s49 integer DEFAULT 0,
   s50 integer DEFAULT 0,
   s51 integer DEFAULT 0,
   s52 integer DEFAULT 0,
   s53 integer DEFAULT 0,
   s54 integer DEFAULT 0,
   s55 integer DEFAULT 0,
   s56 integer DEFAULT 0,
   s57 integer DEFAULT 0,
   s58 integer DEFAULT 0,
   s59 integer DEFAULT 0,
   s60 integer DEFAULT 0,
   s61 integer DEFAULT 0,
   s62 integer DEFAULT 0,
   s63 integer DEFAULT 0,
   s64 integer DEFAULT 0,
   s65 integer DEFAULT 0,
   s66 integer DEFAULT 0,
   s67 integer DEFAULT 0,
   s68 integer DEFAULT 0,
   s69 integer DEFAULT 0,
   s70 integer DEFAULT 0,
   s71 integer DEFAULT 0,
   s72 integer DEFAULT 0,
   s73 integer DEFAULT 0,
   s74 integer DEFAULT 0,
   s75 integer DEFAULT 0,
   s76 integer DEFAULT 0,
   s77 integer DEFAULT 0,
   s78 integer DEFAULT 0,
   s79 integer DEFAULT 0,
   s80 integer DEFAULT 0,
   s81 integer DEFAULT 0,
   s82 integer DEFAULT 0,
   s83 integer DEFAULT 0,
   s84 integer DEFAULT 0,
   s85 integer DEFAULT 0,
   s86 integer DEFAULT 0,
   s87 integer DEFAULT 0,
   s88 integer DEFAULT 0,
   s89 integer DEFAULT 0,
   s90 integer DEFAULT 0,
   s91 integer DEFAULT 0,
   s92 integer DEFAULT 0,
   s93 integer DEFAULT 0,
   s94 integer DEFAULT 0,
   s95 integer DEFAULT 0,
   s96 integer DEFAULT 0,
   s97 integer DEFAULT 0,
   s98 integer DEFAULT 0,
   s99 integer DEFAULT 0,
   s100 integer DEFAULT 0,
   s101 integer DEFAULT 0,
   s102 integer DEFAULT 0,
   s103 integer DEFAULT 0,
   s104 integer DEFAULT 0,
   s105 integer DEFAULT 0,
   s106 integer DEFAULT 0,
   s107 integer DEFAULT 0,
   s108 integer DEFAULT 0,
   s109 integer DEFAULT 0,
   s110 integer DEFAULT 0,
   s111 integer DEFAULT 0,
   s112 integer DEFAULT 0,
   s113 integer DEFAULT 0,
   s114 integer DEFAULT 0,
   s115 integer DEFAULT 0,
   s116 integer DEFAULT 0,
   s117 integer DEFAULT 0,
   s118 integer DEFAULT 0,
   s119 integer DEFAULT 0,
   s120 integer DEFAULT 0,
   s121 integer DEFAULT 0,
   s122 integer DEFAULT 0,
   s123 integer DEFAULT 0,
   s124 integer DEFAULT 0,
   s125 integer DEFAULT 0,
   s126 integer DEFAULT 0,
   s127 integer DEFAULT 0,
   s128 integer DEFAULT 0,
   s129 integer DEFAULT 0,
   s130 integer DEFAULT 0,
   s131 integer DEFAULT 0,
   s132 integer DEFAULT 0,
   s133 integer DEFAULT 0,
   s134 integer DEFAULT 0,
   s135 integer DEFAULT 0,
   s136 integer DEFAULT 0,
   s137 integer DEFAULT 0,
   s138 integer DEFAULT 0,
   s139 integer DEFAULT 0,
   s140 integer DEFAULT 0,
   s141 integer DEFAULT 0,
   s142 integer DEFAULT 0,
   s143 integer DEFAULT 0,
   s144 integer DEFAULT 0,
   s145 integer DEFAULT 0,
   s146 integer DEFAULT 0,
   s147 integer DEFAULT 0,
   s148 integer DEFAULT 0,
   s149 integer DEFAULT 0,
   s150 integer DEFAULT 0,
   s151 integer DEFAULT 0,
   s152 integer DEFAULT 0,
   s153 integer DEFAULT 0,
   s154 integer DEFAULT 0,
   s155 integer DEFAULT 0,
   s156 integer DEFAULT 0,
   s157 integer DEFAULT 0,
   s158 integer DEFAULT 0,
   s159 integer DEFAULT 0,
   s160 integer DEFAULT 0,
   s161 integer DEFAULT 0,
   s162 integer DEFAULT 0,
   s163 integer DEFAULT 0,
   s164 integer DEFAULT 0,
   s165 integer DEFAULT 0,
   s166 integer DEFAULT 0,
   s167 integer DEFAULT 0,
   s168 integer DEFAULT 0,
   s169 integer DEFAULT 0,
   s170 integer DEFAULT 0,
   s171 integer DEFAULT 0,
   s172 integer DEFAULT 0,
   s173 integer DEFAULT 0,
   s174 integer DEFAULT 0,
   s175 integer DEFAULT 0,
   s176 integer DEFAULT 0,
   s177 integer DEFAULT 0,
   s178 integer DEFAULT 0,
   s179 integer DEFAULT 0,
   s180 integer DEFAULT 0,
   s181 integer DEFAULT 0,
   s182 integer DEFAULT 0,
   s183 integer DEFAULT 0,
   s184 integer DEFAULT 0,
   s185 integer DEFAULT 0,
   s186 integer DEFAULT 0,
   s187 integer DEFAULT 0,
   s188 integer DEFAULT 0,
   s189 integer DEFAULT 0,
   s190 integer DEFAULT 0,
   s191 integer DEFAULT 0,
   s192 integer DEFAULT 0,
   s193 integer DEFAULT 0,
   s194 integer DEFAULT 0,
   s195 integer DEFAULT 0,
   s196 integer DEFAULT 0,
   s197 integer DEFAULT 0,
   s198 integer DEFAULT 0,
   s199 integer DEFAULT 0,
   s200 integer DEFAULT 0,
   s201 integer DEFAULT 0,
   s202 integer DEFAULT 0,
   s203 integer DEFAULT 0,
   s204 integer DEFAULT 0,
   s205 integer DEFAULT 0,
   s206 integer DEFAULT 0,
   s207 integer DEFAULT 0,
   s208 integer DEFAULT 0,
   s209 integer DEFAULT 0,
   s210 integer DEFAULT 0,
   s211 integer DEFAULT 0,
   s212 integer DEFAULT 0,
   s213 integer DEFAULT 0,
   s214 integer DEFAULT 0,
   s215 integer DEFAULT 0,
   s216 integer DEFAULT 0,
   s217 integer DEFAULT 0,
   s218 integer DEFAULT 0,
   s219 integer DEFAULT 0,
   s220 integer DEFAULT 0,
   s221 integer DEFAULT 0,
   s222 integer DEFAULT 0,
   s223 integer DEFAULT 0,
   s224 integer DEFAULT 0,
   s225 integer DEFAULT 0,
   s226 integer DEFAULT 0,
   s227 integer DEFAULT 0,
   s228 integer DEFAULT 0,
   s229 integer DEFAULT 0,
   s230 integer DEFAULT 0,
   s231 integer DEFAULT 0,
   s232 integer DEFAULT 0,
   s233 integer DEFAULT 0,
   s234 integer DEFAULT 0,
   s235 integer DEFAULT 0,
   s236 integer DEFAULT 0,
   s237 integer DEFAULT 0,
   s238 integer DEFAULT 0,
   s239 integer DEFAULT 0,
   s240 integer DEFAULT 0,
   s241 integer DEFAULT 0,
   s242 integer DEFAULT 0,
   s243 integer DEFAULT 0,
   s244 integer DEFAULT 0,
   s245 integer DEFAULT 0,
   s246 integer DEFAULT 0,
   s247 integer DEFAULT 0,
   s248 integer DEFAULT 0,
   s249 integer DEFAULT 0,
   s250 integer DEFAULT 0,
   s251 integer DEFAULT 0,
   s252 integer DEFAULT 0,
   s253 integer DEFAULT 0,
   s254 integer DEFAULT 0,
   s255 integer DEFAULT 0,
   s256 integer DEFAULT 0,
   s257 integer DEFAULT 0,
   s258 integer DEFAULT 0,
   s259 integer DEFAULT 0,
   s260 integer DEFAULT 0,
   s261 integer DEFAULT 0,
   s262 integer DEFAULT 0,
   s263 integer DEFAULT 0,
   s264 integer DEFAULT 0,
   s265 integer DEFAULT 0,
   s266 integer DEFAULT 0,
   s267 integer DEFAULT 0,
   s268 integer DEFAULT 0,
   s269 integer DEFAULT 0,
   s270 integer DEFAULT 0,
   s271 integer DEFAULT 0,
   s272 integer DEFAULT 0,
   s273 integer DEFAULT 0,
   s274 integer DEFAULT 0,
   s275 integer DEFAULT 0,
   s276 integer DEFAULT 0,
   s277 integer DEFAULT 0,
   s278 integer DEFAULT 0,
   s279 integer DEFAULT 0,
   s280 integer DEFAULT 0,
   s281 integer DEFAULT 0,
   s282 integer DEFAULT 0,
   s283 integer DEFAULT 0,
   s284 integer DEFAULT 0,
   s285 integer DEFAULT 0,
   s286 integer DEFAULT 0,
   s287 integer DEFAULT 0,
   s288 integer DEFAULT 0,
   s289 integer DEFAULT 0,
   s290 integer DEFAULT 0,
   s291 integer DEFAULT 0,
   s292 integer DEFAULT 0,
   s293 integer DEFAULT 0,
   s294 integer DEFAULT 0,
   s295 integer DEFAULT 0,
   s296 integer DEFAULT 0,
   s297 integer DEFAULT 0,
   s298 integer DEFAULT 0,
   s299 integer DEFAULT 0,
   s300 integer DEFAULT 0,
   s301 integer DEFAULT 0,
   s302 integer DEFAULT 0,
   s303 integer DEFAULT 0,
   s304 integer DEFAULT 0,
   s305 integer DEFAULT 0,
   s306 integer DEFAULT 0,
   s307 integer DEFAULT 0,
   s308 integer DEFAULT 0,
   s309 integer DEFAULT 0,
   s310 integer DEFAULT 0,
   s311 integer DEFAULT 0,
   s312 integer DEFAULT 0,
   s313 integer DEFAULT 0,
   s314 integer DEFAULT 0,
   s315 integer DEFAULT 0,
   s316 integer DEFAULT 0,
   s317 integer DEFAULT 0,
   s318 integer DEFAULT 0,
   s319 integer DEFAULT 0,
   s320 integer DEFAULT 0,
   s321 integer DEFAULT 0,
   s322 integer DEFAULT 0,
   s323 integer DEFAULT 0,
   s324 integer DEFAULT 0,
   s325 integer DEFAULT 0,
   s326 integer DEFAULT 0,
   s327 integer DEFAULT 0,
   s328 integer DEFAULT 0,
   s329 integer DEFAULT 0,
   s330 integer DEFAULT 0,
   s331 integer DEFAULT 0,
   s332 integer DEFAULT 0,
   s333 integer DEFAULT 0,
   s334 integer DEFAULT 0,
   s335 integer DEFAULT 0,
   s336 integer DEFAULT 0,
   s337 integer DEFAULT 0,
   s338 integer DEFAULT 0,
   s339 integer DEFAULT 0,
   s340 integer DEFAULT 0,
   s341 integer DEFAULT 0,
   s342 integer DEFAULT 0,
   s343 integer DEFAULT 0,
   s344 integer DEFAULT 0,
   s345 integer DEFAULT 0,
   s346 integer DEFAULT 0,
   s347 integer DEFAULT 0,
   s348 integer DEFAULT 0,
   s349 integer DEFAULT 0,
   s350 integer DEFAULT 0,
   s351 integer DEFAULT 0,
   s352 integer DEFAULT 0,
   s353 integer DEFAULT 0,
   s354 integer DEFAULT 0,
   s355 integer DEFAULT 0,
   s356 integer DEFAULT 0,
   s357 integer DEFAULT 0,
   s358 integer DEFAULT 0,
   s359 integer DEFAULT 0,
   s360 integer DEFAULT 0,
   s361 integer DEFAULT 0,
   s362 integer DEFAULT 0,
   s363 integer DEFAULT 0,
   s364 integer DEFAULT 0,
   s365 integer DEFAULT 0,
   s366 integer DEFAULT 0,
   s367 integer DEFAULT 0,
   s368 integer DEFAULT 0,
   s369 integer DEFAULT 0,
   s370 integer DEFAULT 0,
   s371 integer DEFAULT 0,
   s372 integer DEFAULT 0,
   s373 integer DEFAULT 0,
   s374 integer DEFAULT 0,
   s375 integer DEFAULT 0,
   s376 integer DEFAULT 0,
   s377 integer DEFAULT 0,
   s378 integer DEFAULT 0,
   s379 integer DEFAULT 0,
   s380 integer DEFAULT 0,
   s381 integer DEFAULT 0,
   s382 integer DEFAULT 0,
   s383 integer DEFAULT 0,
   s384 integer DEFAULT 0,
   s385 integer DEFAULT 0,
   s386 integer DEFAULT 0,
   s387 integer DEFAULT 0,
   s388 integer DEFAULT 0,
   s389 integer DEFAULT 0,
   s390 integer DEFAULT 0,
   s391 integer DEFAULT 0,
   s392 integer DEFAULT 0,
   s393 integer DEFAULT 0,
   s394 integer DEFAULT 0,
   s395 integer DEFAULT 0,
   s396 integer DEFAULT 0,
   s397 integer DEFAULT 0,
   s398 integer DEFAULT 0,
   s399 integer DEFAULT 0,
   s400 integer DEFAULT 0,
   s401 integer DEFAULT 0,
   s402 integer DEFAULT 0,
   s403 integer DEFAULT 0,
   s404 integer DEFAULT 0,
   s405 integer DEFAULT 0,
   s406 integer DEFAULT 0,
   s407 integer DEFAULT 0,
   s408 integer DEFAULT 0,
   s409 integer DEFAULT 0,
   s410 integer DEFAULT 0,
   s411 integer DEFAULT 0,
   s412 integer DEFAULT 0,
   s413 integer DEFAULT 0,
   s414 integer DEFAULT 0,
   s415 integer DEFAULT 0,
   s416 integer DEFAULT 0,
   s417 integer DEFAULT 0,
   s418 integer DEFAULT 0,
   s419 integer DEFAULT 0,
   s420 integer DEFAULT 0,
   s421 integer DEFAULT 0,
   s422 integer DEFAULT 0,
   s423 integer DEFAULT 0,
   s424 integer DEFAULT 0,
   s425 integer DEFAULT 0,
   s426 integer DEFAULT 0,
   s427 integer DEFAULT 0,
   s428 integer DEFAULT 0,
   s429 integer DEFAULT 0,
   s430 integer DEFAULT 0,
   s431 integer DEFAULT 0,
   s432 integer DEFAULT 0,
   s433 integer DEFAULT 0,
   s434 integer DEFAULT 0,
   s435 integer DEFAULT 0,
   s436 integer DEFAULT 0,
   s437 integer DEFAULT 0,
   s438 integer DEFAULT 0,
   s439 integer DEFAULT 0,
   s440 integer DEFAULT 0,
   s441 integer DEFAULT 0,
   s442 integer DEFAULT 0,
   s443 integer DEFAULT 0,
   s444 integer DEFAULT 0,
   s445 integer DEFAULT 0,
   s446 integer DEFAULT 0,
   s447 integer DEFAULT 0,
   s448 integer DEFAULT 0,
   s449 integer DEFAULT 0,
   s450 integer DEFAULT 0,
   s451 integer DEFAULT 0,
   s452 integer DEFAULT 0,
   s453 integer DEFAULT 0,
   s454 integer DEFAULT 0,
   s455 integer DEFAULT 0,
   s456 integer DEFAULT 0,
   s457 integer DEFAULT 0,
   s458 integer DEFAULT 0,
   s459 integer DEFAULT 0,
   s460 integer DEFAULT 0,
   s461 integer DEFAULT 0,
   s462 integer DEFAULT 0,
   s463 integer DEFAULT 0,
   s464 integer DEFAULT 0,
   s465 integer DEFAULT 0,
   s466 integer DEFAULT 0,
   s467 integer DEFAULT 0,
   s468 integer DEFAULT 0,
   s469 integer DEFAULT 0,
   s470 integer DEFAULT 0,
   s471 integer DEFAULT 0,
   s472 integer DEFAULT 0,
   s473 integer DEFAULT 0,
   s474 integer DEFAULT 0,
   s475 integer DEFAULT 0,
   s476 integer DEFAULT 0,
   s477 integer DEFAULT 0,
   s478 integer DEFAULT 0,
   s479 integer DEFAULT 0,
   s480 integer DEFAULT 0,
   s481 integer DEFAULT 0,
   s482 integer DEFAULT 0,
   s483 integer DEFAULT 0,
   s484 integer DEFAULT 0,
   s485 integer DEFAULT 0,
   s486 integer DEFAULT 0,
   s487 integer DEFAULT 0,
   s488 integer DEFAULT 0,
   s489 integer DEFAULT 0,
   s490 integer DEFAULT 0,
   s491 integer DEFAULT 0,
   s492 integer DEFAULT 0,
   s493 integer DEFAULT 0,
   s494 integer DEFAULT 0,
   s495 integer DEFAULT 0,
   s496 integer DEFAULT 0,
   s497 integer DEFAULT 0,
   s498 integer DEFAULT 0,
   s499 integer DEFAULT 0,
   s500 integer DEFAULT 0,
   s501 integer DEFAULT 0,
   s502 integer DEFAULT 0,
   s503 integer DEFAULT 0,
   s504 integer DEFAULT 0,
   s505 integer DEFAULT 0,
   s506 integer DEFAULT 0,
   s507 integer DEFAULT 0,
   s508 integer DEFAULT 0,
   s509 integer DEFAULT 0,
   s510 integer DEFAULT 0,
   s511 integer DEFAULT 0,
   s512 integer DEFAULT 0,

   CONSTRAINT primkey PRIMARY KEY (b),
   CONSTRAINT uniqueb UNIQUE (b),
   CONSTRAINT uniquea UNIQUE (a)
)

WITH (
   OIDS=FALSE
);

ALTER TABLE cachetable
   OWNER TO myuser;




CREATE INDEX test_index
   ON cachetable
   USING btree
   (b COLLATE pg_catalog."default");



CREATE INDEX test2_index
   ON cachetable
   USING btree
   (a COLLATE pg_catalog."default");





and my worktable





CREATE TABLE worktable
(
   b text,
   g integer
)

WITH (
   OIDS=FALSE
);

ALTER TABLE worktable
   OWNER TO myuser;







CREATE INDEX worktable_rh_index
   ON worktable
   USING btree
   (b COLLATE pg_catalog."default");







CREATE INDEX worktable_tn_index
   ON worktable
   USING btree
   (g);



Best
Björn


Am 20.09.2014 12:36, schrieb Marc Mamin:
>> At first, thanks for your fast and comprehensive help.
>>
>> The structure of my cache table is
>>
>> a text , b text NOT NULL , c text , d text , e timestamp without
>> timezone DEFAULT now(),  f text, s1 integer DEFAULT 0, s2 integer
>> DEFAULT 0, s3 integer DEFAULT 0, ... ,s512 DEFAULT 0
>
>> additional constraints: primary key (b)  , Unique(b), Unique(a)
>> Indexes : Index on a, Index on b
> This looks redundant. e.g. you don't need a unique index on b if you already have a primary key on it.
> Can you post the complete table definition ?
>
> ...
>
>> One remark which might help: overall 90 - 95 % of the s1-s512 columns
>> are 0. I am only interested in columns not equals 0. Perhaps it would
>> make sense to use and array of json and enumerate only values not equals 0.
> Could you change that to replace 0 values with NULLs?
> This would greatly reduce your table space as Postgres is very efficient about NULLs storage:
> It marks all null values in a bit map within the row header so you just need about one bit per null
> instead of 4 bytes for zeros, and hence get rid of your I/O issue.
>
> regards,
>
> Marc Mamin
> ________________________________________
> Von: pgsql-performance-owner@postgresql.org [pgsql-performance-owner@postgresql.org]" im Auftrag von "Björn
Wittich[Bjoern_Wittich@gmx.de] 
> Gesendet: Samstag, 20. September 2014 09:19
> An: Josh Berkus; pgsql-performance@postgresql.org
> Betreff: Re: [PERFORM] query a table with lots of coulmns
>
> At first, thanks for your fast and comprehensive help.
>
> The structure of my cache table is
>
> a text , b text NOT NULL , c text , d text , e timestamp without
> timezone DEFAULT now(),  f text, s1 integer DEFAULT 0, s2 integer
> DEFAULT 0, s3 integer DEFAULT 0, ... ,s512 DEFAULT 0
>
> additional constraints: primary key (b)  , Unique(b), Unique(a)
> Indexes : Index on a, Index on b
>
> This table has 30 Mio rows ( will increase to 50 Mio) in future
>
> My working table is
>
> b text, g integer
>
> Indexes on b and c
>
>
> This table has 5 Mio rows
>
> Scenario:
>
> What I want to achieve :
>
> SELECT s1,s2,s3,...s512,g,d  from <worktable> INNER JOIN <cachetable>
> USING(b) ORDER BY g
>
>
> The inner join will match at least 95 % of columns of the smaller
> worktable in this example 4,75 mio rows.
>
> Running this query takes several hours until I receive the first
> results. Query analyzing shows that the execution plan is doing 2 seq
> table scans on cache and work table.
>
>
> When I divide this huge statement into
>
> SELECT s1,s2,s3,...s512,g,d  from <worktable> INNER JOIN <cachetable>
> USING(b) WHERE g BETWEEN 1 and 10000 ORDER BY g, SELECT
> s1,s2,s3,...s512,g,d  from <worktable> INNER JOIN <cachetable> USING(b)
> WHERE g BETWEEN 10001 and 20000 ORDER BY g, ....
>
> (I can do this because g i unique and continous id from 1 to N)
>
> The result is fast but fireing parallel requests (4-8 times parallel)
> slows down the retrieval.
>
> Execution plan changes when adding "BETWEEN 1 and 10000" to use the indexes.
>
>
>
> One remark which might help: overall 90 - 95 % of the s1-s512 columns
> are 0. I am only interested in columns not equals 0. Perhaps it would
> make sense to use and array of json and enumerate only values not equals 0.
>
> Statistics on the large table:
> table size: 80 GB
> toast-tablesize: 37 GB
> size of indexes: 17 GB
>
>
> Thanks for your help and ideas
>
> Björn
>
>
>
>
>
> Am 19.09.2014 23:40, schrieb Josh Berkus:
>> On 09/19/2014 04:51 AM, Björn Wittich wrote:
>>> I am relatively new to postgres. I have a table with 500 coulmns and
>>> about 40 mio rows. I call this cache table where one column is a unique
>>> key (indexed) and the 499 columns (type integer) are some values
>>> belonging to this key.
>>>
>>> Now I have a second (temporary) table (only 2 columns one is the key of
>>> my cache table) and I want  do an inner join between my temporary table
>>> and the large cache table and export all matching rows. I found out,
>>> that the performance increases when I limit the join to lots of small
>>> parts.
>>> But it seems that the databases needs a lot of disk io to gather all 499
>>> data columns.
>>> Is there a possibilty to tell the databases that all these colums are
>>> always treated as tuples and I always want to get the whole row? Perhaps
>>> the disk oraganization could then be optimized?
>> PostgreSQL is already a row store, which means by default you're getting
>> all of the columns, and the columns are stored physically adjacent to
>> each other.
>>
>> If requesting only 1 or two columns is faster than requesting all of
>> them, that's pretty much certainly due to transmission time, not disk
>> IO.  Otherwise, please post your schema (well, a truncated version) and
>> your queries.
>>
>> BTW, in cases like yours I've used a INT array instead of 500 columns to
>> good effect; it works slightly better with PostgreSQL's compression.
>>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
>



В списке pgsql-performance по дате отправления:

Предыдущее
От: Marc Mamin
Дата:
Сообщение: Re: query a table with lots of coulmns
Следующее
От: Claudio Freire
Дата:
Сообщение: Re: Yet another abort-early plan disaster on 9.3