Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1X6Sgo-00030D-MS for pgsql-performance@arkaria.postgresql.org; Sun, 13 Jul 2014 22:56:14 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1X6Sgn-0005FR-1A for pgsql-performance@arkaria.postgresql.org; Sun, 13 Jul 2014 22:56:13 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1X6Sgk-0005FG-MA for pgsql-performance@postgresql.org; Sun, 13 Jul 2014 22:56:10 +0000 Received: from atlmail3.turner.com ([157.166.168.181]) by magus.postgresql.org with esmtps (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1X6Sgg-000465-EQ for pgsql-performance@postgresql.org; Sun, 13 Jul 2014 22:56:08 +0000 Received: from pps.filterd (ppmprmsa [127.0.0.1]) by ppmprmsa.turner.com (8.14.4/8.14.4) with SMTP id s6DMu3fX019828 for ; Sun, 13 Jul 2014 18:56:03 -0400 Received: from atltbshub02.turner.com (ATLTBSHUB02.turner.com [10.189.248.156]) by ppmprmsa.turner.com with ESMTP id 1n437g81rs-1 (version=TLSv1/SSLv3 cipher=AES128-SHA bits=128 verify=NOT) for ; Sun, 13 Jul 2014 18:56:03 -0400 Received: from edge.turner.com (10.166.126.156) by ATLTBSHUB02.turner.com (10.189.248.156) with Microsoft SMTP Server (TLS) id 14.3.146.0; Sun, 13 Jul 2014 18:55:59 -0400 Received: from na01-bl2-obe.outbound.protection.outlook.com (207.46.163.209) by edge.turner.com (10.166.126.156) with Microsoft SMTP Server (TLS) id 14.3.195.1; Sun, 13 Jul 2014 18:55:58 -0400 Received: from CO2PR07MB601.namprd07.prod.outlook.com (10.141.229.11) by CO2PR07MB602.namprd07.prod.outlook.com (10.141.229.14) with Microsoft SMTP Server (TLS) id 15.0.985.8; Sun, 13 Jul 2014 22:55:44 +0000 Received: from CO2PR07MB601.namprd07.prod.outlook.com ([10.141.229.11]) by CO2PR07MB601.namprd07.prod.outlook.com ([10.141.229.11]) with mapi id 15.00.0985.008; Sun, 13 Jul 2014 22:55:44 +0000 From: "Magers, James" To: "pgsql-performance@postgresql.org" Subject: Query Performance question Thread-Topic: Query Performance question Thread-Index: AQHPnu0UGRZQXArbRUGg1JATMGQy4A== Date: Sun, 13 Jul 2014 22:55:42 +0000 Message-ID: <1405292142269.59034@turner.com> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [2601:0:bc40:172:ace1:e85c:d229:695d] x-microsoft-antispam: BCL:0;PCL:0;RULEID: x-forefront-prvs: 0271483E06 x-forefront-antispam-report: SFV:NSPM; SFS:(189002)(199002)(377424004)(77982001)(19580395003)(31966008)(83072002)(87936001)(83322001)(92566001)(85852003)(46102001)(80022001)(86362001)(19580405001)(110136001)(229853001)(50986999)(19627405001)(99396002)(20776003)(54356999)(74502001)(19625215002)(85306003)(64706001)(551934003)(76482001)(74662001)(4396001)(92726001)(79102001)(101416001)(81542001)(106116001)(95666004)(106356001)(99286002)(36756003)(2351001)(16236675004)(105586002)(2656002)(21056001)(107886001)(107046002)(81342001); DIR:OUT; SFP:; SCL:1; SRVR:CO2PR07MB602; H:CO2PR07MB601.namprd07.prod.outlook.com; FPR:; MLV:sfv; PTR:InfoNoRecords; MX:1; LANG:en; Content-Type: multipart/alternative; boundary="_000_140529214226959034turnercom_" MIME-Version: 1.0 X-OriginatorOrg: turner.com X-Proofpoint-Virus-Version: vendor=fsecure engine=2.50.10432:5.12.52, 1.0.14, 0.0.0000 definitions=2014-07-13_02:2014-07-11,2014-07-13,1970-01-01 signatures=0 X-Pg-Spam-Score: -4.2 (----) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --_000_140529214226959034turnercom_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I am using a Pentaho process to access the database and select the appropri= ate information to update the DB tables and records. I am trying to select= the previous subscription key in order to update the factable for any reco= rds that have the previous key to have the current subscription key. This q= uery is intended to use the current subscription key and subscription info = to select the previous subscription key to allow for the information to be = updated. I would like to optimize the query to execute more efficiently. The database table has about 60K records in it and when I run an explain an= aylyze it indicates that the query optimizer chooses to execute a bitmap he= ap scan, this seems like an inefficient method for this query. Query: Select subscription_key as prev_sub_key from member_subscription_d where subscription_value =3D'noname@mailinator.com' and newsletter_nme =3D 'newsletter_member' and subscription_platform =3D 'email' and version =3D (select version -1 as mtch_vers from member_subscription_d where subscription_key =3D 4037516) Current Data in Database for this address: subscription_key | version | date_from | date_to = | newsletter_nme | subscription_platform | subscription_value | s= ubscription_status | list_status | current_status | unsubscribetoken | t= ransaction_date | newsletter_sts ------------------+---------+------------------------+---------------------= -------+-------------------+-----------------------+--------------------+--= -------------------+-------------+----------------+------------------+-----= -------------------+---------------- 4001422 | 1 | 2000-02-09 00:00:00-05 | 2014-04-19 09:57:24-= 04 | newsletter_member | email | noname@mailinator.com<= mailto:noname@mailinator.com> | VALID | pending | f= | | 2000-02-09 00:00:00-05 | 2 4019339 | 2 | 2014-04-19 09:57:24-04 | 2014-06-04 12:27:34-= 04 | newsletter_member | email | noname@mailinator.com<= mailto:noname@mailinator.com> | VALID | subscribe | f= | | 2014-04-19 09:57:24-04 | 1 4037516 | 3 | 2014-06-04 12:27:34-04 | 2199-12-31 23:59:59.= 999-05 | newsletter_member | email | noname@mailinator.com<= mailto:noname@mailinator.com> | VALID | subscribe | t= | | 2014-06-04 12:27:34-04 | 1 (3 rows) System information: Postgres Version: 9.2 OS : Linux cmprodpgsql1 3.2.0-37-virtual #58-Ubuntu SMP Thu Jan 24 15:48:03= UTC 2013 x86_64 x86_64 x86_64 GNU/Linux Pentaho: 5.0.1-stable postgresql.conf checkpoint_segments =3D '8' data_directory =3D '/var/lib/postgresql/9.2/main' datestyle =3D 'iso, mdy' default_text_search_config =3D 'pg_catalog.english' effective_cache_size =3D '2GB' external_pid_file =3D '/var/run/postgresql/9.2-main.pid' hba_file =3D '/etc/postgresql/9.2/main/pg_hba.conf' ident_file =3D '/etc/postgresql/9.2/main/pg_ident.conf' listen_addresses =3D '*' log_line_prefix =3D '%t ' max_connections =3D '200' max_wal_senders =3D '3' port =3D 5432 shared_buffers =3D '1024MB' ssl =3D off ssl_cert_file =3D '/etc/ssl/certs/ssl-cert-snakeoil.pem' ssl_key_file =3D '/etc/ssl/certs/ssl-cert-snakeoil.key' timezone =3D 'localtime' unix_socket_directory =3D '/var/run/postgresql' wal_keep_segments =3D '8' wal_level =3D 'hot_standby' work_mem =3D '100MB' --_000_140529214226959034turnercom_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

I am using a Pentaho process to access the database and select the appro= priate information to update the DB tables and records.  I am trying t= o select the previous subscription key in order to update the factable for = any records that have the previous key to have the current subscription key. This query is intended to use the cu= rrent subscription key and subscription info to select the previous subscri= ption key to allow for the information to be updated.  I would like to= optimize the query to execute more efficiently.

The database table has about 60K records in it and when I run an explain= anaylyze it indicates that the query optimizer chooses to execute a bitmap= heap scan, this seems like an inefficient method for this query.

Query:
Select subscription_key as prev_sub_key
from member_subscription_d
where subscription_value =3D'= noname@mailinator.com'
and newsletter_nme =3D 'newsletter_member'
and subscription_platform =3D 'email'
and version =3D (select version -1 as mtch_vers
            &nb= sp;  from member_subscription_d
            &nb= sp;  where subscription_key =3D 4037516)


Current Data in Database for this address:
 subscription_key | version |       date= _from        |    &n= bsp;     date_to      &nb= sp;    |  newsletter_nme   | subscription_pla= tform | subscription_value | subscription_status | list_status | current_st= atus | unsubscribetoken |    transaction_date  &nb= sp; | newsletter_sts
------------------+---------+------------------------+---------= -------------------+-------------------+-----------------------+= ;--------------------+---------------------+-------------+-----= -----------+------------------+------------------------+-------= ---------
          4001422 |  = ;     1 | 2000-02-09 00:00:00-05 | 2014-04-19 09:57:24-= 04     | newsletter_member | email   &nb= sp;            = | noname@mailinator.com &nb= sp;     | VALID      &nbs= p;        | pending   &nb= sp; | f           &n= bsp;  |          &nb= sp;       | 2000-02-09 00:00:00-05 | &nb= sp;            2
          4019339 |  = ;     2 | 2014-04-19 09:57:24-04 | 2014-06-04 12:27:34-= 04     | newsletter_member | email   &nb= sp;            = | noname@mailinator.com &nb= sp;     | VALID      &nbs= p;        | subscribe   | f&nb= sp;            = |            &= nbsp;     | 2014-04-19 09:57:24-04 |   &= nbsp;          1
          4037516 |  = ;     3 | 2014-06-04 12:27:34-04 | 2199-12-31 23:59:59.= 999-05 | newsletter_member | email       = ;          | noname@mailinator.com &nb= sp;     | VALID      &nbs= p;        | subscribe   | t&nb= sp;            = |            &= nbsp;     | 2014-06-04 12:27:34-04 |   &= nbsp;          1
(3 rows)


System information:
Postgres Version: 9.2
OS : Linux cmprodpgsql1 3.2.0-37-virtual #58-Ubuntu SMP Thu Jan 24 15:48:03= UTC 2013 x86_64 x86_64 x86_64 GNU/Linux
Pentaho: 5.0.1-stable

postgresql.conf
checkpoint_segments =3D '8'
data_directory =3D '/var/lib/postgresql/9.2/main'
datestyle =3D 'iso, mdy'
default_text_search_config =3D 'pg_catalog.english'
effective_cache_size =3D '2GB'
external_pid_file =3D '/var/run/postgresql/9.2-main.pid'
hba_file =3D '/etc/postgresql/9.2/main/pg_hba.conf'
ident_file =3D '/etc/postgresql/9.2/main/pg_ident.conf'
listen_addresses =3D '*'
log_line_prefix =3D '%t '
max_connections =3D '200'
max_wal_senders =3D '3'
port =3D 5432
shared_buffers =3D '1024MB'
ssl =3D off
ssl_cert_file =3D '/etc/ssl/certs/ssl-cert-snakeoil.pem'
ssl_key_file =3D '/etc/ssl/certs/ssl-cert-snakeoil.key'
timezone =3D 'localtime'
unix_socket_directory =3D '/var/run/postgresql'
wal_keep_segments =3D '8'
wal_level =3D 'hot_standby'
work_mem =3D '100MB'

--_000_140529214226959034turnercom_--