pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedquery performance question
21+ messages / 11 participants
[nested] [flat]
* query performance question
@ 2006-04-28 11:30 gulsah <gulsah_129@yahoo.com>
0 siblings, 1 reply; 21+ messages in thread
From: gulsah @ 2006-04-28 11:30 UTC (permalink / raw)
To: pgsql-performance
Hi,
I have a performance problem with Postgresql version 8.1 installed on a Fedora Core release 4 (Stentz) with kernel version 2.6.11.
The machine I am working on has 512MB of RAM and Pentium III 800 MHz CPU.
I have only one table in the database which consists of 256 columns and 10000 rows. Each column is of float type and each row corresponds to a vector in my application. What I want to do is to compute the distance between a predefined vector in hand and the ones in the database.
The computation proceeds according to the following pseudocode:
for(i=1; i<=256 ; i++){
distance += abs(x1_i - x2_i);
}
where x1_i denotes the vector in hand's i coordinate and x2_i denotes the i
coordinate of the vector in the database.
The distance computation have to be done for all the vectors in the database
by means of a query and the result set should be sorted in terms of the
computed distances.
When I implement the query and measure the time spent for it in an application
I see that the query is handled in more than 8 seconds which is undesirable in
my application.
Here what I want to ask you all is that, is it a normal performance for a
computer with the properties that I have mentioned above? Is there any solution
in your mind to increase the performance of my query?
To make it more undestandable, I should give the query for vectors with size
3, but in my case their size is 256.
select
id as vectorid,
abs(40.9546-x2_1)+abs(-72.9964-x2_2)+abs(53.5348-x2_3) as distance
from vectordb
order by distance
Thank you all for your help.
-
gulsah
---------------------------------
Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls. Great rates starting at 1¢/min.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: query performance question
@ 2006-05-01 00:36 Dave Dutcher <dave@tridecap.com>
parent: gulsah <gulsah_129@yahoo.com>
0 siblings, 0 replies; 21+ messages in thread
From: Dave Dutcher @ 2006-05-01 00:36 UTC (permalink / raw)
To: 'gulsah' <gulsah_129@yahoo.com>; pgsql-performance
You are pulling a fair amount of data from the database and doing a lot
of computation in the SQL. I'm not sure how fast this query could be
expected to run, but I had one idea. If you've inserted and deleted a
lot into this table, you will need to run vacuum ocasionally. If you
haven't been doing that, I would try a VACUUM FULL ANALYZE on the table.
(That will take a lock on the table and prevent clients from reading
data while it is running.)
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of gulsah
Sent: Friday, April 28, 2006 6:31 AM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] query performance question
Hi,
I have a performance problem with Postgresql version 8.1 installed on a
Fedora Core release 4 (Stentz) with kernel version 2.6.11.
The machine I am working on has 512MB of RAM and Pentium III 800 MHz
CPU.
I have only one table in the database which consists of 256 columns and
10000 rows. Each column is of float type and each row corresponds to a
vector in my application. What I want to do is to compute the distance
between a predefined vector in hand and the ones in the database.
The computation proceeds according to the following pseudocode:
for(i=1; i<=256 ; i++){
distance += abs(x1_i - x2_i);
}
where x1_i denotes the vector in hand's i coordinate and x2_i denotes
the i
coordinate of the vector in the database.
The distance computation have to be done for all the vectors in the
database
by means of a query and the result set should be sorted in terms of the
computed distances.
When I implement the query and measure the time spent for it in an
application
I see that the query is handled in more than 8 seconds which is
undesirable in
my application.
Here what I want to ask you all is that, is it a normal performance for
a
computer with the properties that I have mentioned above? Is there any
solution
in your mind to increase the performance of my query?
To make it more undestandable, I should give the query for vectors with
size
3, but in my case their size is 256.
select
id as vectorid,
abs(40.9546-x2_1)+abs(-72.9964-x2_2)+abs(53.5348-x2_3) as distance
from vectordb
order by distance
Thank you all for your help.
-
gulsah
_____
Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls. Great
<http://us.rd.yahoo.com/mail_us/taglines/postman7/*http://us.rd.yahoo.co
m/evt=39666/*http://messenger.yahoo.com; rates starting at 1¢/min.
^ permalink raw reply [nested|flat] 21+ messages in thread
* query performance question
@ 2008-06-03 07:57 Marcin Citowicki <marcin.citowicki@m4n.nl>
0 siblings, 2 replies; 21+ messages in thread
From: Marcin Citowicki @ 2008-06-03 07:57 UTC (permalink / raw)
To: pgsql-performance
Hello,
I have a table (transactions) containing 61 414 503 rows. The basic
count query (select count(transid) from transactions) takes 138226
milliseconds.
This is the query analysis output:
Aggregate (cost=2523970.79..2523970.80 rows=1 width=8) (actual
time=268964.088..268964.090 rows=1 loops=1);
-> Seq Scan on transactions (cost=0.00..2370433.43 rows=61414943
width=8) (actual time=13.886..151776.860 rows=61414503 loops=1);
Total runtime: 268973.248 ms;
Query has several indexes defined, including one on transid column:
non-unique;index-qualifier;index-name;type;ordinal-position;column-name;asc-or-desc;cardinality;pages;filter-condition
f;<null>;transactions_id_key;3;1;transid;<null>;61414488;168877;<null>;
t;<null>;trans_ip_address_index;3;1;ip_address;<null>;61414488;168598;<null>;
t;<null>;trans_member_id_index;3;1;member_id;<null>;61414488;169058;<null>;
t;<null>;trans_payment_id_index;3;1;payment_id;<null>;61414488;168998;<null>;
t;<null>;trans_status_index;3;1;status;<null>;61414488;169005;<null>;
t;<null>;transactions__time_idx;3;1;time;<null>;61414488;168877;<null>;
t;<null>;transactions_offer_id_idx;3;1;offer_id;<null>;61414488;169017;<null>;
I'm not a dba so I'm not sure if the time it takes to execute this query
is OK or not, it just seems a bit long to me.
I'd appreciate it if someone could share his/her thoughts on this. Is
there a way to make this table/query perform better?
Any query I'm running that joins with transactions table takes forever
to complete, but maybe this is normal for a table this size.
Regards,
Marcin
Attachments:
marcin_citowicki.vcf (text/x-vcard, 132B) download
begin:vcard
fn:Marcin Citowicki
n:Citowicki;Marcin
email;internet:marcin.citowicki@m4n.nl
x-mozilla-html:FALSE
version:2.1
end:vcard
* Re: query performance question
@ 2008-06-03 08:31 hubert depesz lubaczewski <depesz@depesz.com>
parent: Marcin Citowicki <marcin.citowicki@m4n.nl>
1 sibling, 1 reply; 21+ messages in thread
From: hubert depesz lubaczewski @ 2008-06-03 08:31 UTC (permalink / raw)
To: Marcin Citowicki <marcin.citowicki@m4n.nl>; +Cc: pgsql-performance
On Tue, Jun 03, 2008 at 09:57:15AM +0200, Marcin Citowicki wrote:
> I'm not a dba so I'm not sure if the time it takes to execute this query
> is OK or not, it just seems a bit long to me.
This is perfectly OK. count(*) from table is generally slow. There are
some ways to make it faster (depending if you need exact count, or some
estimate).
> I'd appreciate it if someone could share his/her thoughts on this. Is
> there a way to make this table/query perform better?
You can keep the count of elements in this table in separate table, and
update it with triggers.
> Any query I'm running that joins with transactions table takes forever
> to complete, but maybe this is normal for a table this size.
As for other queries - show them, and their explain analyze.
Performance of count(*) is dependent basically only on size of table. In
case of other queries - it might be simple to optimize them. Or
impossible - without knowing the queries it's impossible to tell.
Do you really care about count(*) from 60m+ record table? How often do
you count the records?
Best regards,
depesz
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: query performance question
@ 2008-06-03 08:55 Marcin Citowicki <marcin.citowicki@m4n.nl>
parent: hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 1 reply; 21+ messages in thread
From: Marcin Citowicki @ 2008-06-03 08:55 UTC (permalink / raw)
To: depesz@depesz.com; +Cc: pgsql-performance
Hello Hubert,
Thank you for your reply. I don't really need to count rows in
transactions table, I just thought this was a good example to show how
slow the query was.
But based on what you wrote it looks like count(*) is slow in general,
so this seems to be OK since the table is rather large.
I just ran other queries (joining transactions table) and they returned
quickly, which leads me to believe that there could be a problem not
with the database, but with the box
the db is running on. Sometimes those same queries take forever and now
they complete in no time at all, so perhaps there is a process that is
running periodically which is slowing the db down.
I'll need to take a look at this.
Thank you for your help!
Marcin
hubert depesz lubaczewski wrote:
> On Tue, Jun 03, 2008 at 09:57:15AM +0200, Marcin Citowicki wrote:
>
>> I'm not a dba so I'm not sure if the time it takes to execute this query
>> is OK or not, it just seems a bit long to me.
>>
>
> This is perfectly OK. count(*) from table is generally slow. There are
> some ways to make it faster (depending if you need exact count, or some
> estimate).
>
>
>> I'd appreciate it if someone could share his/her thoughts on this. Is
>> there a way to make this table/query perform better?
>>
>
> You can keep the count of elements in this table in separate table, and
> update it with triggers.
>
>
>> Any query I'm running that joins with transactions table takes forever
>> to complete, but maybe this is normal for a table this size.
>>
>
> As for other queries - show them, and their explain analyze.
>
> Performance of count(*) is dependent basically only on size of table. In
> case of other queries - it might be simple to optimize them. Or
> impossible - without knowing the queries it's impossible to tell.
>
> Do you really care about count(*) from 60m+ record table? How often do
> you count the records?
>
> Best regards,
>
> depesz
>
>
Attachments:
marcin_citowicki.vcf (text/x-vcard, 142B) download
begin:vcard
fn:Marcin Citowicki
n:Citowicki;Marcin
email;internet:marcin.citowicki@m4n.nl
x-mozilla-html:FALSE
version:2.1
end:vcard
* Re: query performance question
@ 2008-06-03 09:04 tv@fuzzy.cz
parent: Marcin Citowicki <marcin.citowicki@m4n.nl>
1 sibling, 1 reply; 21+ messages in thread
From: tv@fuzzy.cz @ 2008-06-03 09:04 UTC (permalink / raw)
To: Marcin Citowicki <marcin.citowicki@m4n.nl>; +Cc: pgsql-performance
Hi,
Hubert already answered your question - it's expected behavior, the
count(*) has to read all the tuples from the table (even dead ones!). So
if you have a really huge table, it will take a long time to read it.
There are several ways to speed it up - some of them are simple (but the
speedup is limited), some of them require change of application logic and
requires to rewrite part of the application (using triggers to count the
rows, etc.)
1) If the transactions have sequential ID without gaps, you may easily
select MAX(id) and that'll give the count. This won't work if some of the
transactions were deleted or if you need to use other filtering criteria.
The needed changes in the application are quite small (basically just a
single SQL query).
2) Move the table to a separate tablespace (a separate disk if possible).
This will speed up the reads, as the table will be 'compact'. This is just
a db change, it does not require change in the application logic. This
will give you some speedup, but not as good as 1) or 3).
3) Build a table with totals or maybe subtotals, updated by triggers. This
requires serious changes in application as well as in database, but solves
issues of 1) and may give you even better results.
Tomas
> Hello,
>
> I have a table (transactions) containing 61 414 503 rows. The basic
> count query (select count(transid) from transactions) takes 138226
> milliseconds.
> This is the query analysis output:
>
> Aggregate (cost=2523970.79..2523970.80 rows=1 width=8) (actual
> time=268964.088..268964.090 rows=1 loops=1);
> -> Seq Scan on transactions (cost=0.00..2370433.43 rows=61414943
> width=8) (actual time=13.886..151776.860 rows=61414503 loops=1);
> Total runtime: 268973.248 ms;
>
> Query has several indexes defined, including one on transid column:
>
> non-unique;index-qualifier;index-name;type;ordinal-position;column-name;asc-or-desc;cardinality;pages;filter-condition
>
> f;<null>;transactions_id_key;3;1;transid;<null>;61414488;168877;<null>;
> t;<null>;trans_ip_address_index;3;1;ip_address;<null>;61414488;168598;<null>;
> t;<null>;trans_member_id_index;3;1;member_id;<null>;61414488;169058;<null>;
> t;<null>;trans_payment_id_index;3;1;payment_id;<null>;61414488;168998;<null>;
> t;<null>;trans_status_index;3;1;status;<null>;61414488;169005;<null>;
> t;<null>;transactions__time_idx;3;1;time;<null>;61414488;168877;<null>;
> t;<null>;transactions_offer_id_idx;3;1;offer_id;<null>;61414488;169017;<null>;
>
> I'm not a dba so I'm not sure if the time it takes to execute this query
> is OK or not, it just seems a bit long to me.
> I'd appreciate it if someone could share his/her thoughts on this. Is
> there a way to make this table/query perform better?
> Any query I'm running that joins with transactions table takes forever
> to complete, but maybe this is normal for a table this size.
> Regards,
>
> Marcin
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: query performance question
@ 2008-06-04 00:10 PFC <lists@peufeu.com>
parent: Marcin Citowicki <marcin.citowicki@m4n.nl>
0 siblings, 0 replies; 21+ messages in thread
From: PFC @ 2008-06-04 00:10 UTC (permalink / raw)
To: Marcin Citowicki <marcin.citowicki@m4n.nl>; depesz@depesz.com; +Cc: pgsql-performance
> Thank you for your reply. I don't really need to count rows in
> transactions table, I just thought this was a good example to show how
> slow the query was.
Usually you're more interested in the performance of the queries you need
to make rather than the ones you don't need to make ;)
> But based on what you wrote it looks like count(*) is slow in general,
> so this seems to be OK since the table is rather large.
Well any query that needs to scan 60 million rows will be slow...
Now understand that this is not a problem with count(*) which can be very
fast if you "select count(*) where..." and the condition in the where
produces a reasonable number of rows to count, it is just a problem of
having to scan the 60 million rows. But fortunately since it is perfectly
useless to know the rowcount of this 60 million table with a perfect
precision you never need to make this query ;)
> I just ran other queries (joining transactions table) and they returned
> quickly, which leads me to believe that there could be a problem not
> with the database, but with the box
> the db is running on. Sometimes those same queries take forever and now
> they complete in no time at all, so perhaps there is a process that is
> running periodically which is slowing the db down.
Then if you have specific queries that you need to optimize you will need
to run EXPLAIN ANALYZE on them and post the results, when they are fast
and when they are slow to see if there is a difference in plans. Also the
output from vmstat in times of big slowness can provide useful
information. Crosschecking with your cron jobs, etc is a good idea. Also
the usual suspects, like are your tables VACUUM'd and ANALYZE'd etc.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: query performance question
@ 2008-06-05 15:43 Dan Harris <fbsd@drivefaster.net>
parent: tv@fuzzy.cz
0 siblings, 1 reply; 21+ messages in thread
From: Dan Harris @ 2008-06-05 15:43 UTC (permalink / raw)
To: pgsql-performance
tv@fuzzy.cz wrote:
>
> 3) Build a table with totals or maybe subtotals, updated by triggers. This
> requires serious changes in application as well as in database, but solves
> issues of 1) and may give you even better results.
>
> Tomas
>
>
I have tried this. It's not a magic bullet. We do our billing based on
counts from huge tables, so accuracy is important to us. I tried
implementing such a scheme and ended up abandoning it because the
summary table became so full of dead tuples during and after large bulk
inserts that it slowed down selects on that table to an unacceptable
speed. Even with a VACUUM issued every few hundred inserts, it still
bogged down due to the constant churn of the inserts.
I ended up moving this count tracking into the application level. It's
messy and only allows a single instance of an insert program due to the
localization of the counts in program memory, but it was the only way I
found to avoid the penalty of constant table churn on the triggered inserts.
-Dan
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: query performance question
@ 2008-06-05 17:16 Kenneth Marshall <ktm@rice.edu>
parent: Dan Harris <fbsd@drivefaster.net>
0 siblings, 1 reply; 21+ messages in thread
From: Kenneth Marshall @ 2008-06-05 17:16 UTC (permalink / raw)
To: Dan Harris <fbsd@drivefaster.net>; +Cc: pgsql-performance
Dan,
Did you try this with 8.3 and its new HOT functionality?
Ken
On Thu, Jun 05, 2008 at 09:43:06AM -0600, Dan Harris wrote:
> tv@fuzzy.cz wrote:
>>
>> 3) Build a table with totals or maybe subtotals, updated by triggers. This
>> requires serious changes in application as well as in database, but solves
>> issues of 1) and may give you even better results.
>>
>> Tomas
>>
>>
> I have tried this. It's not a magic bullet. We do our billing based on
> counts from huge tables, so accuracy is important to us. I tried
> implementing such a scheme and ended up abandoning it because the summary
> table became so full of dead tuples during and after large bulk inserts
> that it slowed down selects on that table to an unacceptable speed. Even
> with a VACUUM issued every few hundred inserts, it still bogged down due to
> the constant churn of the inserts.
> I ended up moving this count tracking into the application level. It's
> messy and only allows a single instance of an insert program due to the
> localization of the counts in program memory, but it was the only way I
> found to avoid the penalty of constant table churn on the triggered
> inserts.
>
> -Dan
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: query performance question
@ 2008-06-05 20:22 Dan Harris <fbsd@drivefaster.net>
parent: Kenneth Marshall <ktm@rice.edu>
0 siblings, 0 replies; 21+ messages in thread
From: Dan Harris @ 2008-06-05 20:22 UTC (permalink / raw)
To: pgsql-performance
Kenneth Marshall wrote:
> Dan,
>
> Did you try this with 8.3 and its new HOT functionality?
>
> Ken
>
I did not. I had to come up with the solution before we were able to
move to 8.3. But, Tom did mention that the HOT might help and I forgot
about that when writing the prior message. I'm in the midst of moving
30 databases from 8.0 to 8.3 at the moment but when I'm finished, I
might have time to test it.
-Dan
^ permalink raw reply [nested|flat] 21+ messages in thread
* Query Performance question
@ 2014-07-13 22:55 Magers, James <James.Magers@turner.com>
0 siblings, 1 reply; 21+ messages in thread
From: Magers, James @ 2014-07-13 22:55 UTC (permalink / raw)
To: 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@mailinator.com'<mailto:='noname@mailinator.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@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 = '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'
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Query Performance question
@ 2014-07-14 00:58 Tomas Vondra <tv@fuzzy.cz>
parent: Magers, James <James.Magers@turner.com>
0 siblings, 1 reply; 21+ messages in thread
From: Tomas Vondra @ 2014-07-14 00:58 UTC (permalink / raw)
To: pgsql-performance
On 14.7.2014 00:55, Magers, James wrote:
> 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.
Why do you think it's inefficient? The planner thinks it's efficient,
for some reason. And it's impossible to say if that's a good decision,
because we don't know (a) the explain plan, and (b) structure of the
table involved (indexes, ...).
Please post the explain analyze output to explain.depesz.com and post
the link here (it's more readable than posting it here directly).
Also, please do this:
SELECT relname, relpages, reltuples
FROM pg_class WHERE relname = 'member_subscription_d'
and this
\d member_subscription_d
and post the results here.
regards
Tomas
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Query Performance question
@ 2014-07-14 02:20 Magers, James <James.Magers@turner.com>
parent: Tomas Vondra <tv@fuzzy.cz>
0 siblings, 1 reply; 21+ messages in thread
From: Magers, James @ 2014-07-14 02:20 UTC (permalink / raw)
To: Tomas Vondra <tv@fuzzy.cz>; pgsql-performance
Tomas,
Thank you for your feedback. I am attaching the requested information. While I do not think the query is necessarily inefficient, I believe a sequence scan would be more efficient.
\d member_subscription_d
Table "public.member_subscription_d"
Column | Type | Modifiers
-----------------------+--------------------------+-----------------------------------------------------------------
subscription_key | bigint | not null default nextval('subscription_id_seq'::regclass)
version | integer | not null
date_from | timestamp with time zone |
date_to | timestamp with time zone |
newsletter_nme | character varying(50) |
subscription_platform | character varying(50) |
subscription_value | character varying(255) |
subscription_status | character varying(100) |
list_status | character varying(25) |
current_status | boolean |
unsubscribetoken | character varying(200) |
transaction_date | timestamp with time zone |
newsletter_sts | integer |
Indexes:
"member_subscription_key" PRIMARY KEY, btree (subscription_key)
"idx_member_subscription_d_list_status" btree (list_status)
"idx_member_subscription_d_newsletter_nme" btree (newsletter_nme)
"idx_member_subscription_d_subscription_status" btree (subscription_status)
"idx_member_subscription_d_subscription_value" btree (subscription_value)
"idx_member_subscription_d_tk" btree (subscription_key)
Referenced by:
TABLE "member_recipient_f" CONSTRAINT "member_subscription_d_recipient_f_fk" FOREIGN KEY (subscription_key) REFERENCES member_subscription_d(subscription_key)
pgahq_datamart-# FROM pg_class WHERE relname = 'member_subscription_d';
relname | relpages | reltuples
-----------------------+----------+-----------
member_subscription_d | 1383 | 63012
(1 row)
Explain output:
http://explain.depesz.com/s/OVK
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Query Performance question
@ 2014-07-14 07:07 Thomas Kellerer <spam_eater@gmx.net>
parent: Magers, James <James.Magers@turner.com>
0 siblings, 1 reply; 21+ messages in thread
From: Thomas Kellerer @ 2014-07-14 07:07 UTC (permalink / raw)
To: pgsql-performance
Magers, James, 14.07.2014 04:20:
> Thank you for your feedback. I am attaching the requested information.
> While I do not think the query is necessarily inefficient, I believe a sequence scan would be more efficient.
You can try
set enable_indexscan = off;
set enable_bitmapscan = off;
and then run your query.
But I would be very surprised if a seq scan (which reads through the whole table) was faster than those 4ms you have now
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Query Performance question
@ 2014-07-14 13:18 Magers, James <James.Magers@turner.com>
parent: Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 1 reply; 21+ messages in thread
From: Magers, James @ 2014-07-14 13:18 UTC (permalink / raw)
To: Thomas Kellerer <spam_eater@gmx.net>; pgsql-performance
Thomas,
Thank you. I executed the query this morning after disabling the scan types. I am including links to explain.depesz output for each of the three variations that I executed.
indexscan and bitmapscan off: http://explain.depesz.com/s/sIx
seqscan and bitmapscan off: http://explain.depesz.com/s/GfM
bitmapscan off: http://explain.depesz.com/s/3wna
Thank you,
James
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Query Performance question
@ 2014-07-14 13:48 Thomas Kellerer <spam_eater@gmx.net>
parent: Magers, James <James.Magers@turner.com>
0 siblings, 1 reply; 21+ messages in thread
From: Thomas Kellerer @ 2014-07-14 13:48 UTC (permalink / raw)
To: pgsql-performance
Magers, James, 14.07.2014 15:18:
> Thank you. I executed the query this morning after disabling the scan types.
> I am including links to explain.depesz output for each of the three variations that I executed.
>
> indexscan and bitmapscan off: http://explain.depesz.com/s/sIx
> seqscan and bitmapscan off: http://explain.depesz.com/s/GfM
> bitmapscan off: http://explain.depesz.com/s/3wna
>
So the original query (using an "Index Scan" + "Bitmap Index Scan") is indeed the most efficient one: 4ms vs. 39ms vs. 64ms
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Query Performance question
@ 2014-07-14 14:00 Magers, James <James.Magers@turner.com>
parent: Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 1 reply; 21+ messages in thread
From: Magers, James @ 2014-07-14 14:00 UTC (permalink / raw)
To: Thomas Kellerer <spam_eater@gmx.net>; pgsql-performance
Thomas,
I would have to agree that the current results do indicate that. However, I have run this explain analyze multiple times and the timing varies from about 4ms to 35ms using the Bitmap Heap Scan. Here is an explain plan from Thursday of last week that shows about 21ms. Part of the issue in trying to isolate if the query can be faster is that once the data is cached any way that the query is executed appears to be quicker.
http://explain.depesz.com/s/SIX1
Thank you,
James
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Query Performance question
@ 2014-07-14 14:48 Tomas Vondra <tv@fuzzy.cz>
parent: Magers, James <James.Magers@turner.com>
0 siblings, 1 reply; 21+ messages in thread
From: Tomas Vondra @ 2014-07-14 14:48 UTC (permalink / raw)
To: Magers, James <James.Magers@turner.com>; +Cc: Thomas Kellerer <spam_eater@gmx.net>; pgsql-performance
On 14 Červenec 2014, 16:00, Magers, James wrote:
> Thomas,
>
> I would have to agree that the current results do indicate that. However,
> I have run this explain analyze multiple times and the timing varies from
> about 4ms to 35ms using the Bitmap Heap Scan. Here is an explain plan
> from Thursday of last week that shows about 21ms. Part of the issue in
> trying to isolate if the query can be faster is that once the data is
> cached any way that the query is executed appears to be quicker.
>
> http://explain.depesz.com/s/SIX1
I think that judging the performance based on this limited number of
samples is futile, especially when the plans are this fast. The
measurements are easy to influence by other tasks running on the system,
OS process scheduling etc. Or it might be because of memory pressure on
the system, causing the important data from page cache (and thus I/O for
queries accessing them).
This might be the reason why you saw higher timings, and it's impossible
to say based solely on explain plan from a single execution. To get
meaningful numbers it's necessary to execute the query repeatedly, to
eliminate caching effects. But the question is whether these caching
effects will happen on production or not. (Because what if you tweak the
configuration to get the best plan based on assumption that everything is
cached, when it won't be in practice?)
That being said, the only plan that's actually faster than the bitmap
index scan (which you believe is inefficient) is this one
http://explain.depesz.com/s/3wna
The reason why it's not selected by the optimizer is that the cost is
estimated to be 20.60, while the bitmap index scan cost is estimated as
20.38. So the optimizer decides that 20.38 is lower than 20.60, and thus
chooses the bitmap index scan.
What you may do is tweak cost constants, described here
www.postgresql.org/docs/9.4/static/runtime-config-query.html#RUNTIME-CONFIG-QUERY-CONSTANTS
You need to increase the bitmap idex scan cost estimate, so that it's more
expensive than the index scan. I'd guess that increasing the
cpu_tuple_cost and/or cpu_index_tuple_cost a bit should do the trick.
regards
Tomas
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Query Performance question
@ 2014-07-14 16:02 Magers, James <James.Magers@turner.com>
parent: Tomas Vondra <tv@fuzzy.cz>
0 siblings, 1 reply; 21+ messages in thread
From: Magers, James @ 2014-07-14 16:02 UTC (permalink / raw)
To: Tomas Vondra <tv@fuzzy.cz>; +Cc: Thomas Kellerer <spam_eater@gmx.net>; pgsql-performance
Tomas,
Thank you for the recommendation. In this case, The bitmap scan runs quite quickly, however in production were data may or may not be cached and at higher volumes I am trying to ensure the process will continue to execute efficiently and reduce the impact of the process on other processes running against the database.
My assessment is based on my experiences with the scans. Does your experience provide you with a different assessment of the scan types and how efficient they may be?
Thank you,
James
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Query Performance question
@ 2014-07-14 16:28 Tomas Vondra <tv@fuzzy.cz>
parent: Magers, James <James.Magers@turner.com>
0 siblings, 1 reply; 21+ messages in thread
From: Tomas Vondra @ 2014-07-14 16:28 UTC (permalink / raw)
To: Magers, James <James.Magers@turner.com>; +Cc: Tomas Vondra <tv@fuzzy.cz>; Thomas Kellerer <spam_eater@gmx.net>; pgsql-performance
On 14 Červenec 2014, 18:02, Magers, James wrote:
> Tomas,
>
> Thank you for the recommendation. In this case, The bitmap scan runs
> quite quickly, however in production were data may or may not be cached
> and at higher volumes I am trying to ensure the process will continue to
> execute efficiently and reduce the impact of the process on other
> processes running against the database.
That's why it's important to do the testing with representative amount of
data. Testing the queries on significantly reduced dataset is pointless,
because the optimizer will do different decisions.
> My assessment is based on my experiences with the scans. Does your
> experience provide you with a different assessment of the scan types and
> how efficient they may be?
No. Because I don't have your data. And it seems that your assessment is
based on experience with dataset that's very different from your expected
production dataset, which means the experience is not directly applicable.
The optimizer considers the size of the dataset when choosing the plan.
regards
Tomas
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Query Performance question
@ 2014-07-14 17:15 Magers, James <James.Magers@turner.com>
parent: Tomas Vondra <tv@fuzzy.cz>
0 siblings, 0 replies; 21+ messages in thread
From: Magers, James @ 2014-07-14 17:15 UTC (permalink / raw)
To: Tomas Vondra <tv@fuzzy.cz>; +Cc: Thomas Kellerer <spam_eater@gmx.net>; pgsql-performance
Thank you Tomas. I did execute the queries against a dataset that was representative of what we expect the production dataset to have. By higher volume I meant more transactions happening against the data, We would expect the data size to increase over time and when we executed against a dataset that was about 4x larger the index scan was selected to perform the lookup versus the bitmap heap scan. The scan of the both the smaller and larger datasets were returning in similar times between the two groups of tests. This is part of the reason that I was thinking that the bitmap heap scan may not be as efficient since 4 times the data returned in just a little more time using the index scan.
Thank you,
James
-----Original Message-----
From: Tomas Vondra [mailto:tv@fuzzy.cz]
Sent: Monday, July 14, 2014 12:29 PM
To: Magers, James
Cc: Tomas Vondra; Thomas Kellerer; pgsql-performance@postgresql.org
Subject: RE: [PERFORM] Query Performance question
On 14 Červenec 2014, 18:02, Magers, James wrote:
> Tomas,
>
> Thank you for the recommendation. In this case, The bitmap scan runs
> quite quickly, however in production were data may or may not be cached
> and at higher volumes I am trying to ensure the process will continue to
> execute efficiently and reduce the impact of the process on other
> processes running against the database.
That's why it's important to do the testing with representative amount of
data. Testing the queries on significantly reduced dataset is pointless,
because the optimizer will do different decisions.
> My assessment is based on my experiences with the scans. Does your
> experience provide you with a different assessment of the scan types and
> how efficient they may be?
No. Because I don't have your data. And it seems that your assessment is
based on experience with dataset that's very different from your expected
production dataset, which means the experience is not directly applicable.
The optimizer considers the size of the dataset when choosing the plan.
regards
Tomas
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 21+ messages in thread
end of thread, other threads:[~2014-07-14 17:15 UTC | newest]
Thread overview: 21+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-28 11:30 query performance question gulsah <gulsah_129@yahoo.com>
2006-05-01 00:36 ` Dave Dutcher <dave@tridecap.com>
2008-06-03 07:57 query performance question Marcin Citowicki <marcin.citowicki@m4n.nl>
2008-06-03 08:31 ` hubert depesz lubaczewski <depesz@depesz.com>
2008-06-03 08:55 ` Marcin Citowicki <marcin.citowicki@m4n.nl>
2008-06-04 00:10 ` PFC <lists@peufeu.com>
2008-06-03 09:04 ` tv@fuzzy.cz
2008-06-05 15:43 ` Dan Harris <fbsd@drivefaster.net>
2008-06-05 17:16 ` Kenneth Marshall <ktm@rice.edu>
2008-06-05 20:22 ` Dan Harris <fbsd@drivefaster.net>
2014-07-13 22:55 Query Performance question Magers, James <James.Magers@turner.com>
2014-07-14 00:58 ` Re: Query Performance question Tomas Vondra <tv@fuzzy.cz>
2014-07-14 02:20 ` Re: Query Performance question Magers, James <James.Magers@turner.com>
2014-07-14 07:07 ` Re: Query Performance question Thomas Kellerer <spam_eater@gmx.net>
2014-07-14 13:18 ` Re: Query Performance question Magers, James <James.Magers@turner.com>
2014-07-14 13:48 ` Re: Query Performance question Thomas Kellerer <spam_eater@gmx.net>
2014-07-14 14:00 ` Re: Query Performance question Magers, James <James.Magers@turner.com>
2014-07-14 14:48 ` Re: Query Performance question Tomas Vondra <tv@fuzzy.cz>
2014-07-14 16:02 ` Re: Query Performance question Magers, James <James.Magers@turner.com>
2014-07-14 16:28 ` Re: Query Performance question Tomas Vondra <tv@fuzzy.cz>
2014-07-14 17:15 ` Re: Query Performance question Magers, James <James.Magers@turner.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox