Query Performance question

From: "Magers, James" <James(dot)Magers(at)turner(dot)com>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Query Performance question
Date: 2014-07-13 22:55:42
Message-ID: 1405292142269.59034@turner.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

I am using a Pentaho process to access the database and select the appropriate 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 records that have the previous key to have the current subscription key. This query 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 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 ='noname(at)mailinator(dot)com'<mailto:='noname(at)mailinator(dot)com'>
and newsletter_nme = 'newsletter_member'
and subscription_platform = 'email'
and version = (select version -1 as mtch_vers
from member_subscription_d
where subscription_key = 4037516)

Current Data in Database for this address:
subscription_key | version | date_from | date_to | newsletter_nme | subscription_platform | subscription_value | subscription_status | list_status | current_status | unsubscribetoken | transaction_date | newsletter_sts
------------------+---------+------------------------+----------------------------+-------------------+-----------------------+--------------------+---------------------+-------------+----------------+------------------+------------------------+----------------
4001422 | 1 | 2000-02-09 00:00:00-05 | 2014-04-19 09:57:24-04 | newsletter_member | email | noname(at)mailinator(dot)com<mailto:noname(at)mailinator(dot)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(at)mailinator(dot)com<mailto:noname(at)mailinator(dot)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(at)mailinator(dot)com<mailto:noname(at)mailinator(dot)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 = '8'
data_directory = '/var/lib/postgresql/9.2/main'
datestyle = 'iso, mdy'
default_text_search_config = 'pg_catalog.english'
effective_cache_size = '2GB'
external_pid_file = '/var/run/postgresql/9.2-main.pid'
hba_file = '/etc/postgresql/9.2/main/pg_hba.conf'
ident_file = '/etc/postgresql/9.2/main/pg_ident.conf'
listen_addresses = '*'
log_line_prefix = '%t '
max_connections = '200'
max_wal_senders = '3'
port = 5432
shared_buffers = '1024MB'
ssl = off
ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
ssl_key_file = '/etc/ssl/certs/ssl-cert-snakeoil.key'
timezone = 'localtime'
unix_socket_directory = '/var/run/postgresql'
wal_keep_segments = '8'
wal_level = 'hot_standby'
work_mem = '100MB'

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tomas Vondra 2014-07-14 00:58:08 Re: Query Performance question
Previous Message Emre Hasegeli 2014-07-12 08:31:15 Re: GIN index not used