Alessandro Ipe | 17 Dec 16:14 2014
Picon

Excessive memory used for INSERT

Hi,

 

 

Software and hardware running postgresql are:

- postgresql92-9.2.3-1.1.1.x86_64

- openSuSE 12.3 x64_86

- 16 GB of RAM

- 2 GB of swap

- 8-core Intel(R) Xeon(R) CPU E5-2407 0 <at> 2.20GHz

- ext4 filesystem hold on a hardware Dell PERC H710 RAID10 with 4x4TB SATA HDs.

- 2 GB of RAM are reserved for a virtual machine.

 

The single database used was created by

CREATE FUNCTION msg_function() RETURNS trigger

LANGUAGE plpgsql

AS $_$ DECLARE _tablename text; _date text; _slot timestamp; BEGIN _slot := NEW.slot; _date := to_char(_slot, 'YYYY-MM-DD'); _tablename := 'MSG_'||_date; PERFORM 1 FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = 'r' AND c.relname = _tablename AND n.nspname = 'public'; IF NOT FOUND THEN EXECUTE 'CREATE TABLE public.' || quote_ident(_tablename) || ' ( ) INHERITS (public.MSG)'; EXECUTE 'ALTER TABLE public.' || quote_ident(_tablename) || ' OWNER TO seviri'; EXECUTE 'GRANT ALL ON TABLE public.' || quote_ident(_tablename) || ' TO seviri'; EXECUTE 'ALTER TABLE ONLY public.' || quote_ident(_tablename) || ' ADD CONSTRAINT ' || quote_ident(_tablename||'_pkey') || ' PRIMARY KEY (slot,msg)'; END IF; EXECUTE 'INSERT INTO public.' || quote_ident(_tablename) || ' VALUES ($1.*)' USING NEW; RETURN NULL; END; $_$;

 

CREATE TABLE msg (

slot timestamp(0) without time zone NOT NULL,

msg integer NOT NULL,

hrv bytea,

vis006 bytea,

vis008 bytea,

ir_016 bytea,

ir_039 bytea,

wv_062 bytea,

wv_073 bytea,

ir_087 bytea,

ir_097 bytea,

ir_108 bytea,

ir_120 bytea,

ir_134 bytea,

pro bytea,

epi bytea,

clm bytea,

tape character varying(10)

);

 

Basically, this database consists of daily tables with the date stamp appended in their names, i.e.

MSG_YYYY-MM-DD and a global table MSG linked to these tables allowing to list all the records.

 

A cron script performing a single insert (upsert, see log excerpt below) runs every 15 minutes and

never had any issue.

 

However, I also need to submit historical records. This is achieved by a bash script parsing a text file

and building insert commands which are submitted 10 at a time to the database using psql through a

temp file in a BEGIN; ...; COMMIT block. When running this script, I noticed that the INSERT

subprocess can reached around 4GB of memory using htop (see attached screenshot). After a while,

the script inevitably crashes with the following messages

psql:/tmp/tmp.a0ZrivBZhD:10: connection to server was lost

Could not submit SQL request file /tmp/tmp.a0ZrivBZhD to database

 

and the associated entries in the log:

2014-12-15 17:54:07 GMT LOG: server process (PID 21897) was terminated by signal 9: Killed

2014-12-15 17:54:07 GMT DETAIL: Failed process was running: WITH upsert AS (update MSG set (slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE) = (to_timestamp('201212032145', 'YYYYMMDDHH24MI'),2,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_020') where slot=to_timestamp('201212032145', 'YYYYMMDDHH24MI') and MSG=2 RETURNING *) insert into MSG (slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE) select to_timestamp('201212032145', 'YYYYMMDDHH24MI'),2,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_020' WHERE NOT EXISTS (SELECT * FROM upsert);

2014-12-15 17:54:07 GMT LOG: terminating any other active server processes

2014-12-15 17:54:07 GMT WARNING: terminating connection because of crash of another server process

2014-12-15 17:54:07 GMT DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

2014-12-15 17:54:07 GMT HINT: In a moment you should be able to reconnect to the database and repeat your command.

2014-12-15 17:54:07 GMT seviri seviri WARNING: terminating connection because of crash of another server process

2014-12-15 17:54:07 GMT seviri seviri DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

2014-12-15 17:54:07 GMT seviri seviri HINT: In a moment you should be able to reconnect to the database and repeat your command.

2014-12-15 17:54:07 GMT LOG: all server processes terminated; reinitializing

2014-12-15 17:54:08 GMT LOG: database system was interrupted; last known up at 2014-12-15 17:49:38 GMT

2014-12-15 17:54:08 GMT LOG: database system was not properly shut down; automatic recovery in progress

2014-12-15 17:54:08 GMT LOG: redo starts at 0/58C1C060

2014-12-15 17:54:08 GMT LOG: record with zero length at 0/58C27950

2014-12-15 17:54:08 GMT LOG: redo done at 0/58C27920

2014-12-15 17:54:08 GMT LOG: last completed transaction was at log time 2014-12-15 17:53:33.898086+00

2014-12-15 17:54:08 GMT LOG: autovacuum launcher started

2014-12-15 17:54:08 GMT LOG: database system is ready to accept connections

 

My postgresql.conf contains the following modified parameters:

listen_addresses = '*'

max_connections = 100

shared_buffers = 96MB # increased from the default value of 24MB, because script was failing in the beginning

 

together with the /etc/sysctl.conf settings:

sys.kernel.shmmax = 268435456

sys.kernel.shmall = 268435456

 

Am I doing something wrong, either in my database definition (function, upsert) or in the parameters

used (shared buffers) ? Or is such memory consumption usual ?

 

 

Many thanks,

 

 

Alessandro.


--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Picon
Picon

Re: Postgres slave not catching up (on 9.2)

> dbnasg403-12a:/vol/dodpupdbtst03 on /ORA/dbs03/PUPDBTST type nfs (rw,
> actimeo=0,hard,nointr,rsize=65536,wsize=65536,tcp,timeo=600)
> dbnasg401-12a:/vol/dodpupdbtst02 on /ORA/dbs02/PUPDBTST type nfs (rw,
> actimeo=0,hard,nointr,rsize=65536,wsize=65536,tcp,timeo=600)
> 
> to
> 
> dbnasg403-12a:/vol/dodpupdbtst03 on /ORA/dbs03/PUPDBTST type nfs
> (rw,noatime,hard,nointr,rsize=65536,wsize=65536,tcp,timeo=600)
> dbnasg401-12a:/vol/dodpupdbtst02 on /ORA/dbs02/PUPDBTST type nfs
> (rw,noatime,hard,nointr,rsize=65536,wsize=65536,tcp,timeo=600)
> 
> it did have a big impact. The profile of the recovery process on terms of calls
> changed quite a lot:
> 
> From:
> 
> [postgres <at> itrac1202 tmp]$  strace -p 9596 -c Process 9596 attached -
> interrupt to quit Process 9596 detached
> % time     seconds  usecs/call     calls    errors syscall
> ------ ----------- ----------- --------- --------- ----------------
> 78.73    0.217824           0    456855    381376 read
> 17.87    0.049453           0    515320           lseek
>   2.89    0.007989          12       669       669 poll
>   0.33    0.000912          25        36           open
>   0.07    0.000206           0       994       994 stat
>   0.05    0.000151           0       995       787 rt_sigreturn
>   0.05    0.000133           0       673           write
>   0.00    0.000000           0        36           close
>   0.00    0.000000           0        52           kill
> ------ ----------- ----------- --------- --------- ----------------
> 100.00    0.276668                975630    383826 total
> 

This one should read:

[root <at> itrac1202 ~]# strace -c -p 28073
Process 28073 attached - interrupt to quit

Process 28073 detached
% time     seconds  usecs/call     calls    errors syscall
------ ----------- ----------- --------- --------- ----------------
59.16   10.756007           5   2201974   1202832 read
40.69    7.398247           3   2367885           lseek
  0.14    0.025970         154       169           open
  0.00    0.000057           0       169           close
  0.00    0.000038           0       169           kill
  0.00    0.000033           1        29           write
  0.00    0.000000           0         1           semop
------ ----------- ----------- --------- --------- ----------------
100.00   18.180352               4570396   1202832 total


Apologies for the confusion.

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Picon
Picon

Re: Postgres slave not catching up (on 9.2)

 
> > The slave (I don't have control on the master) is using 2 NFS file
> > systems, one for WALs and another one for the data, on Netapp controllers:
> >
> > dbnasg401-12a:/vol/dodpupdbtst02 on /ORA/dbs02/PUPDBTST type nfs
> >
> (rw,remount,hard,nointr,rsize=32768,wsize=32768,tcp,actimeo=0,timeo=60
> > 0)
> >
> > dbnasg403-12a:/vol/dodpupdbtst03 on /ORA/dbs03/PUPDBTST type nfs
> >
> (rw,remount,hard,nointr,rsize=32768,wsize=32768,tcp,actimeo=0,timeo=60
> > 0)
> 
> You should use noatime to avoid unnecessary IO.
> 

Just to mention that changing the mount points from:

dbnasg403-12a:/vol/dodpupdbtst03 on /ORA/dbs03/PUPDBTST type nfs (rw, actimeo=0,hard,nointr,rsize=65536,wsize=65536,tcp,timeo=600)
dbnasg401-12a:/vol/dodpupdbtst02 on /ORA/dbs02/PUPDBTST type nfs (rw, actimeo=0,hard,nointr,rsize=65536,wsize=65536,tcp,timeo=600)

to

dbnasg403-12a:/vol/dodpupdbtst03 on /ORA/dbs03/PUPDBTST type nfs (rw,noatime,hard,nointr,rsize=65536,wsize=65536,tcp,timeo=600)
dbnasg401-12a:/vol/dodpupdbtst02 on /ORA/dbs02/PUPDBTST type nfs (rw,noatime,hard,nointr,rsize=65536,wsize=65536,tcp,timeo=600)

it did have a big impact. The profile of the recovery process on terms of calls changed quite a lot:

From: 

[postgres <at> itrac1202 tmp]$  strace -p 9596 -c
Process 9596 attached - interrupt to quit
Process 9596 detached
% time     seconds  usecs/call     calls    errors syscall
------ ----------- ----------- --------- --------- ----------------
78.73    0.217824           0    456855    381376 read
17.87    0.049453           0    515320           lseek
  2.89    0.007989          12       669       669 poll
  0.33    0.000912          25        36           open
  0.07    0.000206           0       994       994 stat
  0.05    0.000151           0       995       787 rt_sigreturn
  0.05    0.000133           0       673           write
  0.00    0.000000           0        36           close
  0.00    0.000000           0        52           kill
------ ----------- ----------- --------- --------- ----------------
100.00    0.276668                975630    383826 total


To:

[postgres <at> itrac1202 tmp]$  strace -p 9596 -c
Process 9596 attached - interrupt to quit
Process 9596 detached
% time     seconds  usecs/call     calls    errors syscall
------ ----------- ----------- --------- --------- ----------------
78.73    0.217824           0    456855    381376 read
17.87    0.049453           0    515320           lseek
  2.89    0.007989          12       669       669 poll
  0.33    0.000912          25        36           open
  0.07    0.000206           0       994       994 stat
  0.05    0.000151           0       995       787 rt_sigreturn
  0.05    0.000133           0       673           write
  0.00    0.000000           0        36           close
  0.00    0.000000           0        52           kill
------ ----------- ----------- --------- --------- ----------------
100.00    0.276668                975630    383826 total

We did also increased the shared_buffers from 12 to 24GB.

The lag has decreased most of the time:

*/10 * * * *     /usr/local/pgsql/postgresql-9.2.9/bin/psql -U postgres -h /var/lib/pgsql/ -p 6600 -d
puppetdb -c "SELECT now(), now() - pg_last_xact_replay_timestamp()  AS time_lag" | perl -ne 'if
(/\|\s+(\d{2}):(\d{2}):(\d{2})\.\d+/) {$hour=$1;$min=$2;$sec=$3; print $_;}' >> /tmp/lag929morememmount.log


...
2014-12-14 14:10:01.688947+01 | 00:00:00.096524
 2014-12-14 14:20:01.798223+01 | 00:00:00.024083
 2014-12-14 14:30:01.884448+01 | 00:00:00.420791
 2014-12-14 14:40:01.960623+01 | 00:00:00.168318
 2014-12-14 14:50:01.191487+01 | 00:00:00.163832
 2014-12-14 15:00:02.146436+01 | 00:00:00.026934
 2014-12-14 15:10:01.277963+01 | 00:00:00.332185
 2014-12-14 15:20:01.353979+01 | 00:00:00.020616
 2014-12-14 15:30:01.417092+01 | 00:00:00.584768
 2014-12-14 15:40:01.575347+01 | 00:00:00.151685
 2014-12-14 15:50:01.205507+01 | 00:00:00.102073
 2014-12-14 16:00:01.321511+01 | 00:00:00.590677
 2014-12-14 16:10:01.570474+01 | 00:00:00.182683
 2014-12-14 16:20:01.640095+01 | 00:00:00.420185
 2014-12-14 16:30:01.767033+01 | 00:00:00.015989
 2014-12-14 16:40:01.849532+01 | 00:00:00.106296
 2014-12-14 16:50:01.920876+01 | 00:00:00.258851
 2014-12-14 17:00:02.000278+01 | 00:00:00.119841
 2014-12-14 17:10:01.894227+01 | 00:00:00.091599
 2014-12-14 17:20:01.61729+01 | 00:00:00.367367
 2014-12-14 17:30:01.683326+01 | 00:00:00.103884
 2014-12-14 17:40:01.755904+01 | 00:00:00.051262
 2014-12-14 17:50:01.833825+01 | 00:00:00.06901
 2014-12-14 18:00:01.901236+01 | 00:00:00.17467
 2014-12-14 18:10:01.186283+01 | 00:00:00.214941
 2014-12-14 18:20:01.145413+01 | 00:00:00.03517
 2014-12-14 18:30:01.241746+01 | 00:00:00.207842
 2014-12-14 18:40:01.299413+01 | 00:00:00.147878
 2014-12-14 18:50:01.368541+01 | 00:00:00.393893
 2014-12-14 19:00:01.430736+01 | 00:00:00.031226
 2014-12-14 19:10:01.672117+01 | 00:05:03.512832
 2014-12-14 19:20:01.9195+01 | 00:06:39.08761
 2014-12-14 19:30:02.184486+01 | 00:00:00.307668
 2014-12-14 19:40:01.227278+01 | 00:00:00.054831
 2014-12-14 19:50:01.305485+01 | 00:00:00.425595
 2014-12-14 20:00:01.410501+01 | 00:00:00.394526
 2014-12-14 20:10:01.984196+01 | 00:00:00.388844
 2014-12-14 20:20:01.031042+01 | 00:00:00.503092
 2014-12-14 20:30:01.225871+01 | 00:00:00.241493
 2014-12-14 20:40:01.305696+01 | 00:00:00.280656
 2014-12-14 20:50:01.379617+01 | 00:00:00.151103
 2014-12-14 21:00:01.468849+01 | 00:00:00.014412
 2014-12-14 21:10:01.724514+01 | 00:00:00.147476
 2014-12-14 21:20:01.799292+01 | 00:00:00.08696
 2014-12-14 21:30:01.866336+01 | 00:00:00.035226
 2014-12-14 21:40:01.942882+01 | 00:00:00.111701
 2014-12-14 21:50:02.010419+01 | 00:00:00.215121
 2014-12-14 22:00:01.110033+01 | 00:00:16.460612
 2014-12-14 22:10:01.568286+01 | 00:00:00.077897
 2014-12-14 22:20:01.682714+01 | 00:00:00.104112
 2014-12-14 22:30:01.758958+01 | 00:00:00.061474
 2014-12-14 22:40:01.970545+01 | 00:00:00.108613
 2014-12-14 22:50:01.038908+01 | 00:00:00.039637
 2014-12-14 23:00:01.120295+01 | 00:00:00.338731
 2014-12-14 23:10:01.365371+01 | 00:00:00.680065
 2014-12-14 23:20:01.423365+01 | 00:00:00.154614
 2014-12-14 23:30:01.48998+01 | 00:00:00.014643
 2014-12-14 23:40:01.569452+01 | 00:00:00.126961
 2014-12-14 23:50:01.63047+01 | 00:00:00.303156
 2014-12-15 00:00:01.278047+01 | 00:00:00.351391
 2014-12-15 00:10:01.382566+01 | 00:00:00.012265
 2014-12-15 00:20:01.444746+01 | 00:07:39.002651
 2014-12-15 00:30:01.510413+01 | 00:16:13.476753
 2014-12-15 00:40:01.97735+01 | 00:00:00.105011
 2014-12-15 00:50:01.082528+01 | 00:01:10.313796
 2014-12-15 01:00:01.124843+01 | 00:00:01.508016
 2014-12-15 01:10:01.818415+01 | 00:00:00.082441
 2014-12-15 01:20:01.961064+01 | 00:00:00.048221
 2014-12-15 01:30:01.355472+01 | 00:00:00.37941
 2014-12-15 01:40:01.42728+01 | 00:00:00.013836
 2014-12-15 01:50:01.486446+01 | 00:00:00.110321
 2014-12-15 02:00:01.566731+01 | 00:00:00.290281
 2014-12-15 02:10:01.236574+01 | 00:01:15.954532
 2014-12-15 02:20:01.440259+01 | 00:00:00.471677
 2014-12-15 02:30:01.5733+01 | 00:00:00.208574
 2014-12-15 02:40:01.662425+01 | 00:00:00.591091
 2014-12-15 02:50:01.263385+01 | 00:00:00.050648
 2014-12-15 03:00:01.340777+01 | 00:00:00.289115
 2014-12-15 03:10:01.993079+01 | 00:00:00.790201
 2014-12-15 03:20:01.061826+01 | 00:00:00.043176
 2014-12-15 03:30:01.125639+01 | 00:00:00.172924
 2014-12-15 03:40:01.252033+01 | 00:03:05.113579
 2014-12-15 03:50:01.362396+01 | 00:00:00.254974
 2014-12-15 04:00:01.370922+01 | 00:00:00.208254
 2014-12-15 04:10:01.472816+01 | 00:00:00.077214
 2014-12-15 04:20:01.553443+01 | 00:00:00.135887
 2014-12-15 04:30:01.63607+01 | 00:00:00.027272
 2014-12-15 04:40:01.696442+01 | 00:00:00.130954
 2014-12-15 04:50:01.786961+01 | 00:00:00.572573
 2014-12-15 05:00:01.790753+01 | 00:00:00.491799
 2014-12-15 05:10:01.078332+01 | 00:07:58.438202  **** likely autovacuum
 2014-12-15 05:20:01.139541+01 | 00:00:00.057486
 2014-12-15 05:30:01.251079+01 | 00:00:00.053462
 2014-12-15 05:40:01.322349+01 | 00:00:00.084701
 2014-12-15 05:50:01.607937+01 | 00:00:00.205241
 2014-12-15 06:00:01.699406+01 | 00:00:00.121415
 2014-12-15 06:10:01.756047+01 | 00:00:00.20769
 2014-12-15 06:20:01.854222+01 | 00:00:00.03397
 2014-12-15 06:30:02.041054+01 | 00:03:07.271295
 2014-12-15 06:40:01.891882+01 | 00:00:00.263748
 2014-12-15 06:50:01.987809+01 | 00:00:00.155619
 2014-12-15 07:00:01.068556+01 | 00:00:00.119866
 2014-12-15 07:10:01.318478+01 | 00:00:00.092856
 2014-12-15 07:20:01.704899+01 | 00:00:00.106533
 2014-12-15 07:30:01.773268+01 | 00:00:00.135743
 2014-12-15 07:40:01.730152+01 | 00:00:00.06358
 2014-12-15 07:50:01.798179+01 | 00:00:00.529685
 2014-12-15 08:00:01.868205+01 | 00:00:00.194482
 2014-12-15 08:10:01.219339+01 | 00:00:00.063553
 2014-12-15 08:20:01.309426+01 | 00:00:00.056698
 2014-12-15 08:30:01.120431+01 | 00:00:00.425596
 2014-12-15 08:40:01.201882+01 | 00:00:00.00909
 2014-12-15 08:50:01.272526+01 | 00:00:00.019492
 2014-12-15 09:00:01.361022+01 | 00:00:00.423997
 2014-12-15 09:10:01.603702+01 | 00:00:00.066705
 2014-12-15 09:20:01.682277+01 | 00:00:09.251202
 2014-12-15 09:30:01.934477+01 | 00:00:00.311553
 2014-12-15 09:40:02.03221+01 | 00:00:00.125678
 2014-12-15 09:50:01.105372+01 | 00:00:00.294006
 2014-12-15 10:00:01.201109+01 | 00:00:00.014641
 2014-12-15 10:10:01.164478+01 | 00:01:51.375378
 2014-12-15 10:20:01.264589+01 | 00:09:54.476361  **** likely autovacuum
 2014-12-15 10:30:01.351103+01 | 00:00:00.213636
 2014-12-15 10:40:01.623903+01 | 00:00:00.488103
 2014-12-15 10:50:01.768132+01 | 00:00:00.080799
 2014-12-15 11:00:01.880247+01 | 00:00:20.401738
 2014-12-15 11:10:01.215509+01 | 00:00:00.036288
 2014-12-15 11:20:01.265607+01 | 00:00:00.057142
 2014-12-15 11:30:01.343731+01 | 00:00:00.036609
 2014-12-15 11:40:01.41248+01 | 00:00:00.218139
 2014-12-15 11:50:01.48113+01 | 00:00:00.242754
 2014-12-15 12:00:01.685114+01 | 00:00:00.82528
 2014-12-15 12:10:01.995243+01 | 00:02:29.971448
 2014-12-15 12:20:01.962833+01 | 00:00:00.118112
 2014-12-15 12:30:01.100587+01 | 00:00:00.214437
 2014-12-15 12:40:01.226111+01 | 00:00:00.052599
 2014-12-15 12:50:01.300061+01 | 00:00:00.162205
 2014-12-15 13:00:01.4007+01 | 00:00:00.707891
 2014-12-15 13:10:02.005526+01 | 00:00:00.162238
 2014-12-15 13:20:01.072375+01 | 00:00:00.214978
 2014-12-15 13:30:01.446005+01 | 00:00:00.121816
 2014-12-15 13:40:01.483524+01 | 00:00:00.650178
 2014-12-15 13:50:01.796143+01 | 00:00:00.065482
 2014-12-15 14:00:01.886071+01 | 00:00:00.237577
 2014-12-15 14:10:01.134148+01 | 00:00:00.193941
 2014-12-15 14:20:01.199047+01 | 00:00:00.068058
 2014-12-15 14:30:01.27777+01 | 00:00:00.022991
 2014-12-15 14:40:01.361959+01 | 00:00:00.439753
 2014-12-15 14:50:01.421515+01 | 00:00:00.037749
 2014-12-15 15:00:01.500559+01 | 00:00:00.174448
 2014-12-15 15:10:01.811804+01 | 00:06:09.196648  **** likely autovacuum
..



It goes up till a maximum of 25 minutes (for the last two weeks), it looks correlated with an autovacuum at the
master in one of the big tables of the schema. It happens at about 5hours interval. Is there a way to avoid
this ? Should I ask to the master db dba to try to have a more active autovacuum policy?


Thank you,
Ruben

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Josh Berkus | 12 Dec 22:24 2014

Re: Re: [SQL] querying with index on jsonb slower than standard column. Why?

On 12/08/2014 01:39 PM, Tim Dudgeon wrote:
> On 08/12/2014 18:14, Adrian Klaver wrote:
>> Recheck Cond: ((((data ->> 'assay1_ic50'::text))::double precision > 90::double precision) AND
(((data ->> 'assay2_ic50'::text))::double precision < 10::double precision))
>> > 
>> > which means we have to pull the JSONB value out of the tuple, search
>> > it to find the 'assay1_ic50' key, convert the associated value to text
>> > (which is not exactly cheap because *the value is stored as a numeric*),
>> > then reparse that text string into a float8, after which we can use
>> > float8gt.  And then probably do an equivalent amount of work on the way
>> > to making the other comparison.
>> > 
>> > So this says nothing much about the lossy-bitmap code, and a lot about
>> > how the JSONB code isn't very well optimized yet.  In particular, the
>> > decision not to provide an operator that could extract a numeric field
>> > without conversion to text is looking pretty bad here.
> Yes, that bit seemed strange to me. As I understand the value is stored
> internally as numeric, but the only way to access it is as text and then
> cast back to numeric.
> I *think* this is the only way to do it presently?

Yeah, I believe the core problem is that Postgres currently doesn't have
any way to have variadic return times from a function which don't match
variadic input types.  Returning a value as an actual numeric from JSONB
would require returning a numeric from a function whose input type is
text or json.  So a known issue but one which would require a lot of
replumbing to fix.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Sven R. Kunze | 10 Dec 12:31 2014
Picon

When does PostgreSQL collapse subqueries to join?

Hi everybody,

Denis from SO (see his latest comment) advised me to post my question 
here:

http://stackoverflow.com/questions/27363263/when-does-postgresql-collapse-subqueries-to-joins-and-when-not 
Please also read all the comments as they contain valuable data as well.

What we actually have now is that PostgreSQL collapses subqueries to 
joins but a way differently than using "normal joins" by using "Merge 
Semi Joins" or "Nested Loop Semi Joins" (btw an explanation of these 
would be great here :) ). The queries given in the post are reduced to 
the problem at hand and the by-PostgreSQL-optimized version performed 
very well (bit slower than "normal joins"). Regarding our actual query 
however, the still-different query plan leads to a big performance 
issue. We actually need the complete rows of a instead of a.id. I 
prepared the query plans for you (please note, that querie are executed 
empty file and mem chaches):

################ Perfect Plan ###############
We assume all our queries to be equivalent and therefore want PostgreSQL 
to re-plan the others to this one.

explain analyze verbose select * from a where a.id in (select a.id from 
a inner join text_b b1 on (a.id=b1.a_id) inner join text_b b2 on 
(a.id=b2.a_id) where b1.x='x1' and b1.y='y1' and b2.x='x2' and b2.y='y2' 
order by a.date desc limit 20);

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Nested Loop  (cost=183.30..191.34 rows=1 width=135) (actual 
time=812.486..918.561 rows=20 loops=1)
    Output: a.id, a.date, a.content1, a.content2, a.content3, 
a.content4, a.content5, a.content6n, a.content7, a.content8, a.content9
    ->  HashAggregate  (cost=182.87..182.88 rows=1 width=4) (actual 
time=804.866..804.884 rows=20 loops=1)
          Output: a_1.id
          Group Key: a_1.id
          ->  Limit  (cost=182.85..182.86 rows=1 width=8) (actual 
time=804.825..804.839 rows=20 loops=1)
                Output: a_1.id, a_1.date
                ->  Sort  (cost=182.85..182.86 rows=1 width=8) (actual 
time=804.823..804.829 rows=20 loops=1)
                      Output: a_1.id, a_1.date
                      Sort Key: a_1.date
                      Sort Method: top-N heapsort  Memory: 25kB
                      ->  Nested Loop  (cost=1.57..182.84 rows=1 
width=8) (actual time=96.737..803.871 rows=739 loops=1)
                            Output: a_1.id, a_1.date
                            ->  Merge Join  (cost=1.14..178.74 rows=1 
width=8) (actual time=64.829..83.489 rows=739 loops=1)
                                  Output: b1.a_id, b2.a_id
                                  Merge Cond: (b1.a_id = b2.a_id)
                                  ->  Index Only Scan using text_b_y_x_y 
on public.text_b b1  (cost=0.57..163.29 rows=3936 width=4) (actual 
time=34.811..47.328 rows=15195 loops=1)
                                        Output: b1.x, b1.y, b1.a_id
                                        Index Cond: ((b1.x = 'x1'::text) 
AND (b1.y = 'y1'::text))
                                        Heap Fetches: 0
                                  ->  Index Only Scan using text_b_y_x_y 
on public.text_b b2  (cost=0.57..5.49 rows=46 width=4) (actual 
time=22.123..30.940 rows=1009 loops=1)
                                        Output: b2.x, b2.y, b2.a_id
                                        Index Cond: ((b2.x = 'x2'::text) 
AND (b2.y = 'y2'::text))
                                        Heap Fetches: 0
                            ->  Index Only Scan using a_id_date on 
public.a a_1  (cost=0.43..4.09 rows=1 width=8) (actual time=0.970..0.973 
rows=1 loops=739)
                                  Output: a_1.id, a_1.date
                                  Index Cond: (a_1.id = b1.a_id)
                                  Heap Fetches: 0
    ->  Index Scan using a_id_date on public.a  (cost=0.43..8.45 rows=1 
width=135) (actual time=5.677..5.679 rows=1 loops=20)
          Output: a.id, a.date, a.content1, a.content2, a.content3, 
a.content4, a.content5, a.content6n, a.content7, a.content8, a.content9
          Index Cond: (a.id = a_1.id)
  Planning time: 331.190 ms
  Execution time: 918.694 ms

###################### Not so perfect Plan ##################
Because PostgreSQL does not re-plan the id-only query from SO to the 
perfect query, we also see here a performance degradation.

explain analyze verbose select * from a where a.id in (select a.id from 
a where a.id in (select text_b.a_id from text_b where text_b.x='x1' and 
text_b.y='y1') and a.id in (select text_b.a_id from text_b where 
text_b.x='x2' and text_b.y='y2') order by a.date desc limit 20);

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Nested Loop  (cost=384.48..392.51 rows=1 width=135) (actual 
time=1311.680..1426.135 rows=20 loops=1)
    Output: a.id, a.date, a.content1, a.content2, a.content3, 
a.content4, a.content5, a.content6n, a.content7, a.content8, a.content9
    ->  HashAggregate  (cost=384.04..384.05 rows=1 width=4) (actual 
time=1298.447..1298.470 rows=20 loops=1)
          Output: a_1.id
          Group Key: a_1.id
          ->  Limit  (cost=384.03..384.03 rows=1 width=8) (actual 
time=1298.411..1298.426 rows=20 loops=1)
                Output: a_1.id, a_1.date
                ->  Sort  (cost=384.03..384.03 rows=1 width=8) (actual 
time=1298.409..1298.416 rows=20 loops=1)
                      Output: a_1.id, a_1.date
                      Sort Key: a_1.date
                      Sort Method: top-N heapsort  Memory: 25kB
                      ->  Merge Semi Join  (cost=1.57..384.02 rows=1 
width=8) (actual time=160.186..1297.628 rows=739 loops=1)
                            Output: a_1.id, a_1.date
                            Merge Cond: (a_1.id = text_b.a_id)
                            ->  Nested Loop  (cost=1.00..210.76 rows=46 
width=12) (actual time=80.587..1236.967 rows=1009 loops=1)
                                  Output: a_1.id, a_1.date, text_b_1.a_id
                                  ->  Index Only Scan using text_b_y_x_y 
on public.text_b text_b_1  (cost=0.57..5.49 rows=46 width=4) (actual 
time=51.190..63.400 rows=1009 loops=1)
                                        Output: text_b_1.x, text_b_1.y, 
text_b_1.a_id
                                        Index Cond: ((text_b_1.x = 
'x2'::text) AND (text_b_1.y = 'y2'::text))
                                        Heap Fetches: 0
                                  ->  Index Only Scan using a_id_date on 
public.a a_1  (cost=0.43..4.45 rows=1 width=8) (actual time=1.158..1.160 
rows=1 loops=1009)
                                        Output: a_1.id, a_1.date
                                        Index Cond: (a_1.id = text_b_1.a_id)
                                        Heap Fetches: 0
                            ->  Index Only Scan using text_b_y_x_y on 
public.text_b  (cost=0.57..163.29 rows=3936 width=4) (actual 
time=36.963..54.396 rows=15194 loops=1)
                                  Output: text_b.x, text_b.y, text_b.a_id
                                  Index Cond: ((text_b.x = 'x1'::text) 
AND (text_b.y = 'y1'::text))
                                  Heap Fetches: 0
    ->  Index Scan using a_id_date on public.a  (cost=0.43..8.45 rows=1 
width=135) (actual time=6.376..6.378 rows=1 loops=20)
          Output: a.id, a.date, a.content1, a.content2, a.content3, 
a.content4, a.content5, a.content6n, a.content7, a.content8, a.content9
          Index Cond: (a.id = a_1.id)
  Planning time: 248.279 ms
  Execution time: 1426.337 ms

################### Slow Joins ##########################
Directly querying from the join performs worse.

explain analyze verbose select * from a inner join text_b b1 on 
(a.id=b1.a_id) inner join text_b b2 on (a.id=b2.a_id) where b1.x='x1' 
and b1.y='y1' and b2.x='x2' and b2.y='y2' order by a.date desc limit 20;

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Limit  (cost=186.83..186.83 rows=1 width=177) (actual 
time=4133.420..4133.434 rows=20 loops=1)
    Output: a.id, a.date, a.content1, a.content2, a.content3, 
a.content4, a.content5, a.content6n, a.content7, a.content8, a.content9, 
b1.a_id, b1.x, b1.y, b2.a_id, b2.x, b2.y, a.date
    ->  Sort  (cost=186.83..186.83 rows=1 width=177) (actual 
time=4133.417..4133.423 rows=20 loops=1)
          Output: a.id, a.date, a.content1, a.content2, a.content3, 
a.content4, a.content5, a.content6n, a.content7, a.content8, a.content9, 
b1.a_id, b1.x, b1.y, b2.a_id, b2.x, b2.y, a.date
          Sort Key: a.date
          Sort Method: top-N heapsort  Memory: 34kB
          ->  Nested Loop  (cost=1.57..186.82 rows=1 width=177) (actual 
time=109.094..4130.290 rows=739 loops=1)
                Output: a.id, a.date, a.content1, a.content2, 
a.content3, a.content4, a.content5, a.content6n, a.content7, a.content8, 
a.content9, b1.a_id, b1.x, b1.y, b2.a_id, b2.x, b2.y, a.date
                ->  Merge Join  (cost=1.14..178.74 rows=1 width=42) 
(actual time=72.023..94.234 rows=739 loops=1)
                      Output: b1.a_id, b1.x, b1.y, b2.a_id, b2.x, b2.y
                      Merge Cond: (b1.a_id = b2.a_id)
                      ->  Index Only Scan using text_b_y_x_y on 
public.text_b b1  (cost=0.57..163.29 rows=3936 width=21) (actual 
time=36.084..50.308 rows=15195 loops=1)
                            Output: b1.x, b1.y, b1.a_id
                            Index Cond: ((b1.x = 'x1'::text) AND (b1.y = 
'y1'::text))
                            Heap Fetches: 0
                      ->  Index Only Scan using text_b_y_x_y on 
public.text_b b2  (cost=0.57..5.49 rows=46 width=21) (actual 
time=20.227..37.654 rows=1009 loops=1)
                            Output: b2.x, b2.y, b2.a_id
                            Index Cond: ((b2.x = 'x2'::text) AND (b2.y = 
'y2'::text))
                            Heap Fetches: 0
                ->  Index Scan using a_id_date on public.a 
(cost=0.43..8.07 rows=1 width=135) (actual time=5.454..5.457 rows=1 
loops=739)
                      Output: a.id, a.date, a.content1, a.content2, 
a.content3, a.content4, a.content5, a.content6n, a.content7, a.content8, 
a.content9
                      Index Cond: (a.id = b1.a_id)
  Planning time: 332.545 ms
  Execution time: 4133.574 ms

################### Slow Subqueries ##########################
Directly querying from the subqueries performs even worse.

explain analyze verbose select * from a where a.id in (select 
text_b.a_id from text_b where text_b.x='x1' and text_b.y='y1') and a.id 
in (select text_b.a_id from text_b where text_b.x='x2' and 
text_b.y='y2') order by a.date desc limit 20;

QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Limit  (cost=568.02..568.03 rows=1 width=135) (actual 
time=9765.174..9765.190 rows=20 loops=1)
    Output: a.id, a.date, a.content1, a.content2, a.content3, 
a.content4, a.content5, a.content6n, a.content7, a.content8, a.content9
    ->  Sort  (cost=568.02..568.03 rows=1 width=135) (actual 
time=9765.173..9765.180 rows=20 loops=1)
          Output: a.id, a.date, a.content1, a.content2, a.content3, 
a.content4, a.content5, a.content6n, a.content7, a.content8, a.content9
          Sort Key: a.date
          Sort Method: top-N heapsort  Memory: 30kB
          ->  Merge Semi Join  (cost=1.57..568.01 rows=1 width=135) 
(actual time=294.909..9762.978 rows=739 loops=1)
                Output: a.id, a.date, a.content1, a.content2, 
a.content3, a.content4, a.content5, a.content6n, a.content7, a.content8, 
a.content9
                Merge Cond: (a.id = text_b.a_id)
                ->  Nested Loop  (cost=1.00..394.76 rows=46 width=139) 
(actual time=94.441..9668.179 rows=1009 loops=1)
                      Output: a.id, a.date, a.content1, a.content2, 
a.content3, a.content4, a.content5, a.content6n, a.content7, a.content8, 
a.content9, text_b_1.a_id
                      ->  Index Only Scan using text_b_y_x_y on 
public.text_b text_b_1  (cost=0.57..5.49 rows=46 width=4) (actual 
time=52.588..67.307 rows=1009 loops=1)
                            Output: text_b_1.x, text_b_1.y, text_b_1.a_id
                            Index Cond: ((text_b_1.x = 'x2'::text) AND 
(text_b_1.y = 'y2'::text))
                            Heap Fetches: 0
                      ->  Index Scan using a_id_date on public.a 
(cost=0.43..8.45 rows=1 width=135) (actual time=9.485..9.511 rows=1 
loops=1009)
                            Output: a.id, a.date, a.content1, 
a.content2, a.content3, a.content4, a.content5, a.content6n, a.content7, 
a.content8, a.content9
                            Index Cond: (a.id = text_b_1.a_id)
                ->  Index Only Scan using text_b_y_x_y on public.text_b  
(cost=0.57..163.29 rows=3936 width=4) (actual time=22.705..86.822 
rows=15194 loops=1)
                      Output: text_b.x, text_b.y, text_b.a_id
                      Index Cond: ((text_b.x = 'x1'::text) AND (text_b.y 
= 'y1'::text))
                      Heap Fetches: 0
  Planning time: 267.442 ms
  Execution time: 9765.339 m

What needs to be done in order to feed PostgreSQL with the last query 
and achieve the performance of the first one?

Best regards,

-- 
Sven R. Kunze
TBZ-PARIV GmbH, Bernsdorfer Str. 210-212, 09130 Chemnitz
Tel: +49 (0)371 5347916, Fax: +49 (0)371 5347920
e-mail: srkunze <at> tbz-pariv.de
web: www.tbz-pariv.de

Geschäftsführer: Dr. Reiner Wohlgemuth
Sitz der Gesellschaft: Chemnitz
Registergericht: Chemnitz HRB 8543

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Maila Fatticcioni | 10 Dec 11:44 2014
Picon

Tuning the configuration


Hello.
I need to tune a postgres installation I've just made to get a better
performance. I use two identical servers with a hot replication
configuration. The two servers have the following hardware:

Dual Processor Intel Xeon E5-2640V2 20Mb cache 2.00Ghz,
Ram Mem. 32Gb DDR-3 Ecc Registered,
Controller MegaRaid 8-ports 1Gb cache,
4 Enterprise Hdd NL Sas 600 4Tb Sata,
2 Samsung SSD 840 Pro Series 512Gb,
2 Hdd 500 Gb

I made a software raid with the last two hard disks with ext4 and I
installed Ubuntu 14.04.1 LTS (I have to use this SO) on it. I made a
hardware raid with the four SAS hard disks and I mount the partition
on it with ext4 without journaling and I put the database on it.

Now I have two more steps to do.

1- could you please help tuning the configuration? What are the best
value I should use for wal_buffers and shared_buffers?
2- I would like to use the two SDD to store the wal file. Do you think
it is useful or how should I use them?

Thank you for your answers.

Best Regards,
Maila Fatticcioni
Josh Berkus | 10 Dec 02:46 2014

Re: Yet another abort-early plan disaster on 9.3

On 12/05/2014 08:04 AM, Simon Riggs wrote:
> On 6 December 2014 at 00:45, Merlin Moncure <mmoncure <at> gmail.com> wrote:
> 
>> Neat -- got any test cases (would this have prevented OP's problem)?
> 
> No test case was posted, so I am unable to confirm.
> 
> A test case I produced that appears to be the same issue is fixed.
> 
> I await confirmation from the OP.
> 

So that's proprietary/confidential data.  However, the company involved
has a large testbed and I could test their data using a patched version
of Postgres.   In 3 months their data distribution has drifted, so I'll
need to do some work to recreate the original bad plan circumstances.
I'll keep you posted on how the patch works for that setup.

It would be great to come up with a generic/public test for a bad
abort-early situation.  Ideas?

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Strahinja Kustudić | 10 Dec 00:28 2014

8xIntel S3500 SSD in RAID10 on Dell H710p

I have a beast of a Dell server with the following specifications:
  • 4x Xeon E5-4657LV2 (48 cores total)
  • 196GB RAM
  • 2x SCSI 900GB in RAID1 (for the OS)
  • 8x Intel S3500 SSD 240GB in RAID10
  • H710p RAID controller, 1GB cache
Centos 6.6, RAID10 SSDs uses XFS (mkfs.xfs -i size=512 /dev/sdb).

Here are some relevant postgresql.conf settings:
shared_buffers = 8GB
work_mem = 64MB
maintenance_work_mem = 1GB
synchronous_commit = off
checkpoint_segments = 256
checkpoint_timeout = 10min
checkpoint_completion_target = 0.9
seq_page_cost = 1.0
effective_cache_size = 100GB

I ran some "fast" pgbench tests with 4, 6 and 8 drives in RAID10 and here are the results:

time /usr/pgsql-9.1/bin/pgbench -U postgres -i -s 12000 pgbench # 292GB DB

4 drives 6 drives 8 drives
105 min 98 min 94 min

/usr/pgsql-9.1/bin/pgbench -U postgres -c 96 -T 600 -N pgbench   # Write test

4 drives 6 drives 8 drives
6567 7427 8073

/usr/pgsql-9.1/bin/pgbench -U postgres -c 96 -T 600 pgbench  # Read/Write test

4 drives 6 drives 8 drives
3651 5474 7203

/usr/pgsql-9.1/bin/pgbench -U postgres -c 96 -T 600 -S pgbench  # Read test

4 drives 6 drives 8 drives
17628 25482 28698


A few notes:
  • I ran these tests only once, so take these number with reserve. I didn't have the time to run them more times, because I had to test how the server works with our app and it takes a considerable amount of time to run them all.
  • I wanted to use a bigger scale factor, but there is a bug in pgbench with big scale factors.
  • Postgres 9.1 was chosen, since the app which will run on this server uses 9.1.
  • These tests are with the H710p controller set to write-back (WB) and with adaptive read ahead (ADRA). I ran a few tests with write-through (WT) and no read ahead (NORA), but the results were worse.
  • All tests were run using 96 clients as recommended on the pgbench wiki page, but I'm sure I would get better results if I used 48 clients (1 for each core), which I tried with the R/W test and got 7986 on 8 drives, which is almost 800TPS better than with 96 clients.

Since our app is tied to the Postgres performance a lot, I'm currently trying to optimize it. Do you have any suggestions what Postgres/system settings I could try to tweak to increase performance? I have a feeling I could get more performance out of this system.


Regards,
Strahinja
Vivekanand Joshi | 9 Dec 19:42 2014

Hardware Requirements

Hi Team,

 

We are thinking of shifting our data warehouse solution from Netezza to PostgreSQL. I am reading a lot about PostgreSQL lately.

 

Can you please let us know the minimum[overall performance should be good] hardware requirements for the below mentioned statistics. My question is actually divided into two parts.

 

1.       What hardware entities[RAM, Storage, Disk, RAID level etc. ] should we keep in mind while finalizing the requirement? Sorry for my ignorance here as I am totally new to this territory.

2.       What should be the plan of action for performance benchmarking?

3.       What should be the minimum hardware requirements for doing a POC and comparing performance benchmark with Netezza?

 

Parameter

Numbers on 7th Dec 2014

Total number of Users

222

Total number of Application Users

110

Individual Accounts

112

Total number of Successful Queries on 7 December

425124

Total number of Unsuccessful Queries

2591

Maximum number of Queries in an Hour

79920

Maximum number of Queries in a minute when load was maximum in an hour

3143

Maximum number of Queries in a second when load was maximum in an hour

87

Number of Databases

82

Used Space

~1057 GB

Allocated Space

~4453 GB

 

What I actually want to achieve is right now is that running the same load on Netezza and PostgreSQL [300 GB data, 20 concurrent queries, and 30k-40K queries in an hour].

 

I have asked for the following configuration right now.

 

Operating System (Linux 64 bit)

RAM : 8 GB

Storage: 500 GB

CPU Cores: Minimum 4

RAID: Level 10

Disk Type: SATA

 

These figures are for POC only.

 

Does that sound okay? Once again, my trivial questions could be irritating but this is only a start.

 

Warm Regards,


Vivekanand Joshi
+919654227927

 

185 Madison Ave. New York, NY 10016

www.zetainteractive.com

 

Michael Barker | 4 Dec 21:42 2014
Picon

Query doesn't use index on hstore column

Hi,

Apologies if this is the wrong list for this time of query (first time posting).

I'm currently experimenting with hstore on Posgtres 9.4rc1.  I've created a table with an hstore column, with and index on that column (tried both gin and btree indexes) and the explain plan says that the index is never used for the lookup and falls to a sequential scan every time (table has 1 000 000 rows).  The query plans and execution time for btree index, gin index and unindexed are the same.  Is there something I'm doing wrong or missing in order to get indexes to work on hstore columns?

Details:

0) Postgres version:

barkerm=# select version();
                                                    version                                                    
---------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.4rc1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-7), 64-bit
(1 row)

1)  Created table with hstore column and btree index.

barkerm=# \d audit
                                       Table "public.audit"
    Column     |            Type             |                     Modifiers                      
---------------+-----------------------------+----------------------------------------------------
 id            | integer                     | not null default nextval('audit_id_seq'::regclass)
 principal_id  | integer                     | 
 created_at    | timestamp without time zone | 
 root          | character varying(255)      | 
 template_code | character(3)                | 
 attributes    | hstore                      | 
 args          | character varying(255)[]    | 
Indexes:
    "audit_pkey" PRIMARY KEY, btree (id)
    "audit_attributes_idx" btree (attributes)

2) Insert 1 000 000 rows

barkerm=# select count(*) from audit;
  count  
---------
 1000000
(1 row)

3) Run analyse.

4) Pick a row somewhere in the middle:

barkerm=# select id, attributes from audit where id = 500000;
   id   |                       attributes                        
--------+---------------------------------------------------------
 500000 | "accountId"=>"1879355460", "instrumentId"=>"1625557725"
(1 row)

5) Explain query using the attributes column in the where clause (uses Seq Scan).

barkerm=# explain analyse select * from audit where attributes->'accountId' = '1879355460';
                                                 QUERY PLAN                                                 
------------------------------------------------------------------------------------------------------------
 Seq Scan on audit  (cost=0.00..35409.00 rows=5000 width=133) (actual time=114.314..218.821 rows=1 loops=1)
   Filter: ((attributes -> 'accountId'::text) = '1879355460'::text)
   Rows Removed by Filter: 999999
 Planning time: 0.074 ms
 Execution time: 218.843 ms
(5 rows)

6) Rebuild the data using a gin index.

barkerm=# \d audit
                                       Table "public.audit"
    Column     |            Type             |                     Modifiers                      
---------------+-----------------------------+----------------------------------------------------
 id            | integer                     | not null default nextval('audit_id_seq'::regclass)
 principal_id  | integer                     | 
 created_at    | timestamp without time zone | 
 root          | character varying(255)      | 
 template_code | character(3)                | 
 attributes    | hstore                      | 
 args          | character varying(255)[]    | 
Indexes:
    "audit_pkey" PRIMARY KEY, btree (id)
    "audit_attributes_idx" gin (attributes)

7) Again explain the selection of a single row using a constraint that references the hstore column.  Seq Scan is still used.

barkerm=# explain analyse select * from audit where attributes->'accountId' = '1238334838';
                                                 QUERY PLAN                                                 
------------------------------------------------------------------------------------------------------------
 Seq Scan on audit  (cost=0.00..35409.00 rows=5000 width=133) (actual time=122.173..226.363 rows=1 loops=1)
   Filter: ((attributes -> 'accountId'::text) = '1238334838'::text)
   Rows Removed by Filter: 999999
 Planning time: 0.164 ms
 Execution time: 226.392 ms
(5 rows)

8) Drop index an query as a baseline.

barkerm=# explain analyse select * from audit where attributes->'accountId' = '1238334838';
                                                 QUERY PLAN                                                 
------------------------------------------------------------------------------------------------------------
 Seq Scan on audit  (cost=0.00..35409.00 rows=5000 width=133) (actual time=109.115..212.666 rows=1 loops=1)
   Filter: ((attributes -> 'accountId'::text) = '1238334838'::text)
   Rows Removed by Filter: 999999
 Planning time: 0.113 ms
 Execution time: 212.701 ms
(5 rows)

Regards,
Michael Barker.
Josh Berkus | 4 Dec 20:10 2014

Re: issue in postgresql 9.1.3 in using arrow key in Solaris platform

On 11/26/2014 02:16 AM, M Tarkeshwar Rao wrote:
> Hi all,
> 
>  
> 
> We are facing following issue in postgresql 9.1.3 in using arrow key in
> Solaris platform.
> 
> *Can you please help us to resolve it or any new release has fix for
> this or any workaround for this?*

Mr. Rao:

1) Please do not cross-post to multiple mailing lists.  In the future,
this may cause you to be banned from the PostgreSQL mailing lists.

2) PostgreSQL 9.1.3 is 11 patch releases behind and contains multiple
published security holes.

3) Sounds like there's a bug in the readline or libedit libraries for
your platform.  How did you build PostgreSQL?

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


Gmane