[PERFORM] overzealous sorting?
In Mammoth Replicator (PG 8.3) I have a table described as
Table public.tevent_cdr
Column | Type | Modifiers
+--+
event_id | integer | not null default
nextval(('event_id_seq'::text)::regclass)
timestamp | timestamp with time zone | not null
classification | character varying| not null
area | character varying| not null
kind | character varying|
device_id | integer |
device_name| character varying|
fleet_id | integer |
fleet_name | character varying|
customer_id| integer |
customer_name | character varying|
event | text |
Indexes:
tevent_cdr_event_id UNIQUE, btree (event_id)
tevent_cdr_timestamp btree (timestamp)
Check constraints:
tevent_cdr_classification_check CHECK (classification::text
= 'cdr'::text)
Inherits: tevent
This simple query puzzles me. Why does it need to sort the records? Don't they
come from the index in order?
explain analyze select * from tevent_cdr where timestamp =
'2011-09-09 12:00:00.00+0' and timestamp '2011-09-09
13:00:00.00+0' and classification = 'cdr' order by timestamp;
QUERY PLAN
Sort (cost=9270.93..9277.12 rows=2477 width=588) (actual
time=9.219..11.489 rows=2480 loops=1)
Sort Key: timestamp
Sort Method: quicksort Memory: 2564kB
- Bitmap Heap Scan on tevent_cdr (cost=57.93..9131.30 rows=2477
width=588) (actual time=0.440..3.923 rows=2480 loops=1)
Recheck Cond: ((timestamp = '2011-09-09
22:00:00+10'::timestamp with time zone) AND (timestamp '2011-09-09
23:00:00+10'::timestamp with time zone))
Filter: ((classification)::text = 'cdr'::text)
- Bitmap Index Scan on tevent_cdr_timestamp
(cost=0.00..57.31 rows=2477 width=0) (actual time=0.404..0.404 rows=2480
loops=1)
Index Cond: ((timestamp = '2011-09-09
22:00:00+10'::timestamp with time zone) AND (timestamp '2011-09-09
23:00:00+10'::timestamp with time zone))
Total runtime: 13.847 ms
(9 rows)
--
Anthony Shipman | flailover systems: When one goes down it
anthony.ship...@symstream.com | flails about until the other goes down too.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] [PERFORMANCE] Insights: fseek OR read_cluster?
On 24/09/2011 2:49 PM, Antonio Rodriges wrote: Hello, It is interesting how PostgreSQL reads the tablefiie. Whether its indexes store/use filesystem clusters locations containing required data (so it can issue a low level cluster read) or it just fseeks inside a file? What is read_cluster() ? Are you talking about some kind of async and/or direct I/O? If so, PostgreSQL is not designed for direct I/O, it benefits from using the OS's buffer cache, I/O scheduler, etc. IIRC Pg uses pread() to read from its data files, but I didn't go double check in the sources to make sure. -- Craig Ringer -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] [PERFORMANCE] Insights: fseek OR read_cluster?
Thank you, Craig, your answers are always insightful What is read_cluster() ? Are you talking about some kind of async and/or I meant that if you want to read a chunk of data from file you (1) might not call traditional fseek but rather memorize hard drive cluster numbers to boost disk seeks and, (2) perform the read of disk cluster directly. direct I/O? If so, PostgreSQL is not designed for direct I/O, it benefits from using the OS's buffer cache, I/O scheduler, etc. IIRC Pg uses pread() to read from its data files, but I didn't go double check in the sources to make sure. -- Craig Ringer -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] [PERFORMANCE] Insights: fseek OR read_cluster?
Hello, It is interesting how PostgreSQL reads the tablefiie. Whether its indexes store/use filesystem clusters locations containing required data (so it can issue a low level cluster read) or it just fseeks inside a file? Thank you -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Performance Anomaly with col in (A,B) vs. col = A OR col = B ver. 9.0.3
Hi All,
We are currently using PostgreSQL 9.0.3 and we noticed a performance anomaly
from a framework (ActiveRecord) generated query to one of our tables. The
query uses an in clause to check an indexed column for the presence of
either of two values. In this particular case neither of them is present
(but in other cases one or more might be). The framework generates a limit
1 query to test for existence. This query ends up using a seq scan and is
quite slow, however rewriting it using OR = rather then IN uses the index
(as does removing the limit or raising it to a large value). The table has
36 million rows (more details are below) and is read only in typical usage.
I was wondering if IN vs OR planning being so differently represented a bug
and/or if we might have some misconfiguration somewhere that leads the query
planner to pick what in best case can only be a slightly faster plan then
using the index but in worst case is much much slower. I would also think
the cluster on the table would argue against using a sequence scan for this
kind of query (since the hts_code_id's will be colocated, perf, if the id is
present, will very greatly depending on what order the seq scan walks the
table which we've observed...; if the id(s) are not present then this plan
is always terrible). We can use set enable_seqscan TO off around this query
if need be, but it seems like something the planner should have done better
with unless we have something weird somewhere (conf file details are below).
psql (9.0.3)
Type help for help.
-- Table info
dev= ANALYZE exp_detls;
ANALYZE
dev= select count(*) from exp_detls;
36034391
dev=explain analyze select count(*) from exp_detls;
Aggregate (cost=1336141.30..1336141.31 rows=1 width=0) (actual
time=43067.620..43067.621 rows=1 loops=1)
- Seq Scan on exp_detls (cost=0.00..1246046.84 rows=36037784 width=0)
(actual time=0.011..23703.177 rows=36034391 loops=1)
Total runtime: 43067.675 ms
dev=select pg_size_pretty(pg_table_size('exp_detls'));
6919 MB
-- Problematic Query
dev= explain analyze SELECT exp_detls.id FROM exp_detls WHERE
(exp_detls.hts_code_id IN (12,654)) LIMIT 1;
Limit (cost=0.00..158.18 rows=1 width=4) (actual time=9661.363..9661.363
rows=0 loops=1)
- Seq Scan on exp_detls (cost=0.00..1336181.90 rows=8447 width=4)
(actual time=9661.360..9661.360 rows=0 loops=1)
Filter: (hts_code_id = ANY ('{12,654}'::integer[]))
Total runtime: 9661.398 ms
(4 rows)
-- Using OR =, much faster, though more complicated plan then below
dev= explain analyze SELECT exp_detls.id FROM exp_detls WHERE
(exp_detls.hts_code_id = 12 OR exp_detls.hts_code_id = 654) LIMIT 1;
Limit (cost=162.59..166.29 rows=1 width=4) (actual time=0.029..0.029
rows=0 loops=1)
- Bitmap Heap Scan on exp_detls (cost=162.59..31188.14 rows=8370
width=4) (actual time=0.028..0.028 rows=0 loops=1)
Recheck Cond: ((hts_code_id = 12) OR (hts_code_id = 654))
- BitmapOr (cost=162.59..162.59 rows=8370 width=0) (actual
time=0.027..0.027 rows=0 loops=1)
- Bitmap Index Scan on
index_exp_detls_on_hts_code_id_and_data_month (cost=0.00..79.20 rows=4185
width=0) (actual time=0.017..0.017 rows=0 loops=1)
Index Cond: (hts_code_id = 12)
- Bitmap Index Scan on
index_exp_detls_on_hts_code_id_and_data_month (cost=0.00..79.20 rows=4185
width=0) (actual time=0.007..0.007 rows=0 loops=1)
Index Cond: (hts_code_id = 654)
Total runtime: 0.051 ms
(9 rows)
-- No limit, much faster, also a cleaner looking plan (of course problematic
when there are many matching rows)
dev=explain analyze SELECT exp_detls.id FROM exp_detls WHERE
(exp_detls.hts_code_id IN (12,654));
Bitmap Heap Scan on exp_detls (cost=156.93..31161.56 rows=8370 width=4)
(actual time=0.028..0.028 rows=0 loops=1)
Recheck Cond: (hts_code_id = ANY ('{12,654}'::integer[]))
- Bitmap Index Scan on index_exp_detls_on_hts_code_id_and_data_month
(cost=0.00..154.84 rows=8370 width=0) (actual time=0.026..0.026 rows=0
loops=1)
Index Cond: (hts_code_id = ANY ('{12,654}'::integer[]))
Total runtime: 0.045 ms
(5 rows)
-- Table Schema
Table public.exp_detls
Column |Type |
Modifiers
--+-+
id | integer | not null default
nextval('exp_detls_id_seq'::regclass)
created_at | timestamp without time zone | not null
df | integer |
hts_code_id | integer | not null
uscb_country_id | integer |
country_id | integer |
uscb_district_id | integer |
cards_mo | numeric(15,0) | not null
qty_1_mo | numeric(15,0) | not null
qty_2_mo | numeric(15,0) |
[PERFORM] How to find record having % as part of data.
Hi, I am having problem retrieving data with % in the value. I tried a lot but so far i have no luck. for example i have first name = 'abc', middle name = 'pq%', last name = '123' I want to list all the entries that ends with %. Because those are wrong entries and i want to remove % from the middle name and retain 'pq' only. Any help is very much appreciated. Thanks Biswa. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] How to find record having % as part of data.
I already have solution. Just have to use ESCAPE -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Performance Anomaly with col in (A,B) vs. col = A OR col = B ver. 9.0.3
Timothy Garnett tgarn...@panjiva.com writes:
-- Problematic Query
dev= explain analyze SELECT exp_detls.id FROM exp_detls WHERE
(exp_detls.hts_code_id IN (12,654)) LIMIT 1;
Limit (cost=0.00..158.18 rows=1 width=4) (actual time=9661.363..9661.363
rows=0 loops=1)
- Seq Scan on exp_detls (cost=0.00..1336181.90 rows=8447 width=4)
(actual time=9661.360..9661.360 rows=0 loops=1)
Filter: (hts_code_id = ANY ('{12,654}'::integer[]))
Total runtime: 9661.398 ms
(4 rows)
-- Using OR =, much faster, though more complicated plan then below
dev= explain analyze SELECT exp_detls.id FROM exp_detls WHERE
(exp_detls.hts_code_id = 12 OR exp_detls.hts_code_id = 654) LIMIT 1;
Limit (cost=162.59..166.29 rows=1 width=4) (actual time=0.029..0.029
rows=0 loops=1)
- Bitmap Heap Scan on exp_detls (cost=162.59..31188.14 rows=8370
width=4) (actual time=0.028..0.028 rows=0 loops=1)
Recheck Cond: ((hts_code_id = 12) OR (hts_code_id = 654))
- BitmapOr (cost=162.59..162.59 rows=8370 width=0) (actual
time=0.027..0.027 rows=0 loops=1)
- Bitmap Index Scan on
index_exp_detls_on_hts_code_id_and_data_month (cost=0.00..79.20 rows=4185
width=0) (actual time=0.017..0.017 rows=0 loops=1)
Index Cond: (hts_code_id = 12)
- Bitmap Index Scan on
index_exp_detls_on_hts_code_id_and_data_month (cost=0.00..79.20 rows=4185
width=0) (actual time=0.007..0.007 rows=0 loops=1)
Index Cond: (hts_code_id = 654)
Total runtime: 0.051 ms
(9 rows)
Well, the reason it likes the first plan is that that has a smaller
estimated cost ;-). Basically this is a startup-time-versus-total-time
issue: the cost of the seqscan+limit is estimated to be about 1/8447'th
of the time to read the whole table, since it's estimating 8447
candidate matches and assuming that those are uniformly distributed in
the table. Meanwhile, the bitmap scan has a significant startup cost
because the entire indexscan is completed before we start to do any
fetching from the heap. The overestimate of the number of matching
rows contributes directly to overestimating the cost of the indexscan,
too. It ends up being a near thing --- 158 vs 166 cost units --- but
on the basis of these estimates the planner did the right thing.
So, what you need to do to make this better is to get it to have a
better idea of how many rows match the query condition; the overestimate
is both making the expensive plan look cheap, and making the cheaper
plan look expensive. Cranking up the statistics target for the
hts_code_id column (and re-ANALYZEing) ought to fix it. If all your
tables are this large you might want to just increase
default_statistics_target across the board.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Performance Anomaly with col in (A,B) vs. col = A OR col = B ver. 9.0.3
On 9/26/11 10:07 AM, Tom Lane wrote: Cranking up the statistics target for the hts_code_id column (and re-ANALYZEing) ought to fix it. If all your tables are this large you might want to just increase default_statistics_target across the board. regards, tom lane This is common advice in this forum but what's the down side to increasing statistics? With so many questions coming to this forum that are due to insufficient statistics, why not just increase the default_statistics_target? I assume there is a down side, but I've never seen it discussed. Does it increase planning time? Analyze time? Take lots of space? Thanks, Craig -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Performance Anomaly with col in (A,B) vs. col = A OR col = B ver. 9.0.3
Craig James craig_ja...@emolecules.com writes: On 9/26/11 10:07 AM, Tom Lane wrote: Cranking up the statistics target for the hts_code_id column (and re-ANALYZEing) ought to fix it. If all your tables are this large you might want to just increase default_statistics_target across the board. regards, tom lane This is common advice in this forum but what's the down side to increasing statistics? With so many questions coming to this forum that are due to insufficient statistics, why not just increase the default_statistics_target? I assume there is a down side, but I've never seen it discussed. Does it increase planning time? Analyze time? Take lots of space? Yes, yes, and yes. We already did crank up the default default_statistics_target once (in 8.4), so I'm hesitant to do it again. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] [PERFORMANCE] Insights: fseek OR read_cluster?
On Mon, Sep 26, 2011 at 15:51, Antonio Rodriges antonio@gmail.com wrote: What is read_cluster() ? Are you talking about some kind of async and/or I meant that if you want to read a chunk of data from file you (1) might not call traditional fseek but rather memorize hard drive cluster numbers to boost disk seeks and, (2) perform the read of disk cluster directly. PostgreSQL accesses regular files on a file system via lseek(), read() and write() calls, no magic. In modern extent-based file systems, mapping a file offset to a physical disk sector is very fast -- compared to the time of actually accessing the disk. I can't see how direct cluster access would even work, unless you'd give the database direct access to a raw partition, in which case Postgres would effectively have to implement its own file system. The gains are simply not worth it for Postgres, our developer resources are better spent elsewhere. Regards, Marti -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] slow query on tables with new columns added.
I have full access to the database, but no access to the application source code. If creating an index will help, I can do that, but with the columns I don't see it helping as I don't have access to the application source to change that. So yes, by changing settings, I would like to know if there's any memory settings I can change to help or create an index. There is an index on the customer_id column in the gltx table, so I'm not sure what else could be done. If there was a way to create a select trigger, I would do it and return 0 for both columns on that customer_id as it should always be 0. On 09/24/2011 12:10 AM, Filip Rembiałkowski wrote: 2011/9/23 M. D. li...@turnkey.bz I did a software upgrade, and with it came a new feature where when selecting a customer it queries for the sum of a few columns. This takes 7 seconds for the 'Cash Sale' customer - by far the most active customer. I'd like to see if it's possible to get it down a bit by changing settings. To make things clear before we search for a solution. You wrote "by changing settings". Is it the only option? Can't you change the query in software? Can't you change database schema (add indexes etc)? Query: explain analyse select sum(item_points),sum(disc_points) from invoice left join gltx on invoice.invoice_id = gltx.gltx_id where gltx.inactive_on is null and gltx.posted = 'Y' and gltx.customer_id = 'A0ZQ2gsACIsEKLI638ikyg' Aside from other things, you know that LEFT join here is useless? - planner should collapse it to normal join but I'd check. Filip
Re: [PERFORM] slow query on tables with new columns added.
2011/9/26 M. D. li...@turnkey.bz I have full access to the database, but no access to the application source code. If creating an index will help, I can do that, but with the columns I don't see it helping as I don't have access to the application source to change that. So yes, by changing settings, I would like to know if there's any memory settings I can change to help or create an index. There is an index on the customer_id column in the gltx table, so I'm not sure what else could be done. If there was a way to create a select trigger, I would do it and return 0 for both columns on that customer_id as it should always be 0. Hi I didn't respond earlier, because I actually don't see any easy way of speeding up the query. The memory settings seem fine for this size of data. It does not look like you can change things by simply adding indexes. I mean, you can certainly add a specially crafted partial index on gltx.customer_id WHERE (gltx.inactive_on IS NULL) AND (gltx.posted = 'Y') - this can earn you a few percent max. The problem here might be the type of join columns - we can see they are about 10 characters which is not an ideal choice (that's one of reasons why I'm a fan of artificial integer pkeys). You _could_ try running the query with enable_mergejoin = off and see what happens. You can check if the problem persists after dumping and reloading to another db. If app modification was possible, you could materialize the data _before_ it must be queried - using summary table and appropriate triggers for keeping it up-to-date. Regarding your last comment - on that customer_id values should be 0 - if it's a persistent business rule, I would try to create a CHECK to reflect it. With some luck and fiddling, constraint_exclusion might come to help with speeding up your query. Also, if there is something special about customer_id distribution - table partitioning might be an option. Ok, that's a long list - hope this helps, and good luck. After all you can throw more hardware at the problem - or hire some Pg magician :-)
Re: [PERFORM] Performance Anomaly with col in (A,B) vs. col = A OR col = B ver. 9.0.3
On 27/09/2011 1:35 AM, Tom Lane wrote: Craig Jamescraig_ja...@emolecules.com writes: On 9/26/11 10:07 AM, Tom Lane wrote: Cranking up the statistics target for the hts_code_id column (and re-ANALYZEing) ought to fix it. If all your tables are this large you might want to just increase default_statistics_target across the board. regards, tom lane This is common advice in this forum but what's the down side to increasing statistics? With so many questions coming to this forum that are due to insufficient statistics, why not just increase the default_statistics_target? I assume there is a down side, but I've never seen it discussed. Does it increase planning time? Analyze time? Take lots of space? Yes, yes, and yes. We already did crank up the default default_statistics_target once (in 8.4), so I'm hesitant to do it again. This has me wondering about putting together a maintenance/analysis tool that generates and captures stats from several ANALYZE runs and compares them to see if they're reasonably consistent. It then re-runs with higher targets as a one-off, again to see if the stats agree, before restoring the targets to defaults. The tool could crunch comparisons of the resulting stats and warn about tables or columns where the default stats targets aren't sufficient. In the long run this might even be something it'd be good to have Pg do automatically behind the scenes (like autovacuum) - auto-raise stats targets where repeat samplings are inconsistent. Thoughts? Is this reasonable to explore, or a totally bogus idea? I'll see if I can have a play if there's any point to trying it out. -- Craig Ringer -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Performance Anomaly with col in (A,B) vs. col = A OR col = B ver. 9.0.3
Craig Ringer ring...@ringerc.id.au writes: This has me wondering about putting together a maintenance/analysis tool that generates and captures stats from several ANALYZE runs and compares them to see if they're reasonably consistent. It then re-runs with higher targets as a one-off, again to see if the stats agree, before restoring the targets to defaults. The tool could crunch comparisons of the resulting stats and warn about tables or columns where the default stats targets aren't sufficient. It would certainly be useful to have such a tool, but I suspect it's easier said than done. The problem is to know whether the queries on that table are particularly sensitive to having better stats. I think we've largely solved issues having to do with the quality of the histogram (eg, what fraction of the table has values falling into some range), and the remaining trouble spots have to do with predicting the frequency of specific values that are too infrequent to have made it into the most-common-values list. Enlarging the MCV list helps not so much by getting these long-tail values into the MCV list --- they probably still aren't there --- as by allowing us to tighten the upper bound on what the frequency of an unrepresented value must be. So what you have to ask is how many queries care about that. Craig James' example query in this thread is sort of a worst case, because the values it's searching for are in fact not in the table at all. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] overzealous sorting?
On Monday 26 September 2011 19:39, Marc Cousin wrote: Because Index Scans are sorted, not Bitmap Index Scans, which builds a list of pages to visit, to be then visited by the Bitmap Heap Scan step. Marc. Where does this bitmap index scan come from? It seems to negate the advantages of b-tree indexes described in the section Indexes and ORDER BY of the manual. If I do set enable_bitmapscan = off; the query runs a bit faster although with a larger time range it reverts to a sequential scan. -- Anthony Shipman | Consider the set of blacklists that anthony.ship...@symstream.com | do not blacklist themselves... -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] postgres constraint triggers
Hi all I would like to start a dialogue and hear general feedback about the use of constraint triggers in postgres (8.4.5). Our overall issue is that using general triggers is causing slow inserts (from locking issues) in our database. Here are some details: A little background (jboss/j2ee/hibernate/linux). We have 3 basic triggers on a particular database table - one for inserts, one for updates another for deletes and they keep track of a granule count that is used in reporting. This field (gracount) is stored in another table called dataset. An example of the insert trigger/function is as follows: -- CREATE TRIGGER increment_dataset_granule_count AFTER INSERT ON inventory FOR EACH ROW EXECUTE PROCEDURE increment_dataset_granule_count(); CREATE OR REPLACE FUNCTION increment_dataset_granule_count() RETURNS trigger AS $BODY$ DECLARE BEGIN IF NEW.visible_id != 5 THEN UPDATE dataset SET gracount = gracount + 1 WHERE dataset.inv_id = NEW.inv_id; END IF; RETURN NULL; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION increment_dataset_granule_count() OWNER TO jboss; --- What we found was that when these triggers were fired we saw locking issues that slowed down performance dramatically to inserts into the inventory table (the table where the triggers are located). You could see the inserts sit and wait by looking at the pg_stat_activity table. Within our app, the trigger was invoked within the same hibernate transaction that a stateless session bean was using to persist/merge the granule (inventory). Subsequently, in the same transaction, a EJB MBean was merging the dataset changes creating kind of a dead lock on the dataset table. Our first try to solve this problem has been to convert these triggers into a constraint trigger which allows for DEFERRABLE INITIALLY DEFERRED flags. This, we are finding, is forcing the trigger function to run after the triggering transaction is completed. We believe this will fix our locking problem and hopefully speed up our inserts again. Any comments or past experiences would certainly be helpful! thanks, Maria Wilson NASA/Langley Research Center Hampton, Virginia 23681
Re: [PERFORM] Performance Anomaly with col in (A,B) vs. col = A OR col = B ver. 9.0.3
Well, the reason it likes the first plan is that that has a smaller
estimated cost ;-). Basically this is a startup-time-versus-total-time
issue: the cost of the seqscan+limit is estimated to be about 1/8447'th
of the time to read the whole table, since it's estimating 8447
candidate matches and assuming that those are uniformly distributed in
the table. Meanwhile, the bitmap scan has a significant startup cost
because the entire indexscan is completed before we start to do any
fetching from the heap. The overestimate of the number of matching
rows contributes directly to overestimating the cost of the indexscan,
too. It ends up being a near thing --- 158 vs 166 cost units --- but
on the basis of these estimates the planner did the right thing.
So, what you need to do to make this better is to get it to have a
better idea of how many rows match the query condition; the overestimate
is both making the expensive plan look cheap, and making the cheaper
plan look expensive. Cranking up the statistics target for the
hts_code_id column (and re-ANALYZEing) ought to fix it. If all your
tables are this large you might want to just increase
default_statistics_target across the board.
regards, tom lane
Thanks for the great description of what's happening. Very informative.
Upping the stats to the max 1 (from the default 100) makes my example
query use a faster plan, but not other problematic queries we have in the
same vein (just add a few more values to the in clause). For ex. (this is
with stats set to 1 and re-analyzed).
=explain analyze SELECT exp_detls.id FROM exp_detls WHERE
(exp_detls.hts_code_id IN
(8127,8377,8374,10744,11125,8375,8344,9127,9345)) LIMIT 1;
QUERY
PLAN
---
Limit (cost=0.00..152.94 rows=1 width=4) (actual time=12057.637..12057.637
rows=0 loops=1)
- Seq Scan on exp_detls (cost=0.00..1651399.83 rows=10798 width=4)
(actual time=12057.635..12057.635 rows=0 loops=1)
Filter: (hts_code_id = ANY
('{8127,8377,8374,10744,11125,8375,8344,9127,9345}'::integer[]))
Total runtime: 12057.678 ms
From your description I think the planner is making two problematic
assumptions that are leading to our issue:
First is that the potential matches are uniformly distributed across the
physical table. While there are a number of reasons this may not be the
case (correlated insertion order or update patterns etc.), in this case
there's a very clear reason which is that the table is clustered on an index
that leads with the column we're querying against ('hts_code_id') and
nothing has been inserted or updated in the table since the last time we ran
cluster on it (see the schema in the original e-mail).
Second is that is that it's assuming that the IN clause values are actually
present in the table (and at some reasonably large frequency), in the worst
cases (like above) they aren't present at all, forcing a full table scan. I
don't know how the expected freq of values not present in the frequent
values are estimated, but I'm guessing something based on the residual
probability in the stats table and the est. number of distinct values? If
so that assumes that the supplied values actually are in the set of distinct
values which seems unjustified.
Perhaps there should be some estimation on whether the supplied value is one
of the distinct values or not (could probably do something pretty
statistically solid with a not too large bloom filter), or scaling by the
width of the histogram bucket it occurs in (i.e. assuming an even
distribution across the bucket). Though maybe in a lot of common use
situations people only supply values that are known present so maybe this
would make things worse more often then better (maybe limit 1 or better
EXISTS would be a hint the value is not known present). Experimenting a bit
it doesn't seem to matter which values are selected so it's not taking into
account any kind of distribution over the histogram boundaries. For ex this
query:
explain analyze SELECT exp_detls.id FROM exp_detls WHERE
(exp_detls.hts_code_id IN
(2,20001,22,20003,20004,20005,20006,20007,20008)) LIMIT 1;
QUERY
PLAN
---
Limit (cost=0.00..152.94 rows=1 width=4) (actual time=12925.646..12925.646
rows=0 loops=1)
- Seq Scan on exp_detls (cost=0.00..1651399.83 rows=10798 width=4)
(actual time=12925.644..12925.644 rows=0 loops=1)
Filter: (hts_code_id = ANY
('{2,20001,22,20003,20004,20005,20006,20007,20008}'::integer[]))
Total runtime: 12925.677 ms
Has the same expected row count as the earlier query with the same number of
not present IN values, but looking at the histogram boundaries these values
are
[PERFORM] Ineffective autovacuum
Hi all, I have a problem with autovacuum apparently not doing the job I need it to do. I have a table named datasession that is frequently inserted, updated and deleted from. Typically the table will have a few thousand rows in it. Each row typically survives a few days and is updated every 5 - 10 mins. The application receives unreliable, potentially duplicate data from its source, so this table is heavily used for synchronising application threads as well. A typical access pattern is: - tx begin - SELECT FOR UPDATE on a single row - Do some application processing (1 - 100 ms) - Possibly UPDATE the row - tx commit In a few instances of our application we're seeing this table grow obscenely to the point where our monitoring servers get us out of bed to manually vacuum. I like sleep, so I want to fix this =D I've read some recent threads and found a discussion (below) on auto vacuum that mentions auto vacuum will be cancelled when a client requests a lock that auto vacuum is using… My questions: 1) Does it look like I'm affected by the same problem as in the below discussion? 2) Are there better solutions to this problem than a periodic task that vacuums/truncates-and-rebuilds the table? Perhaps relevant info: # select version(); version -- PostgreSQL 8.3.12 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.4 (Ubuntu 4.2.4-1ubuntu3) (1 row) Auto vacuum and vacuum parameters are set to the factory defaults. Cheers, --Royce From: Tom Lane t...@sss.pgh.pa.us Subject: Re: [GENERAL] Vacuum as easily obtained locks Date: 4 August 2011 1:52:02 AM AEST To: Michael Graham mgra...@bloxx.com Cc: Pavan Deolasee pavan.deola...@gmail.com, pgsql-gene...@postgresql.org On Wed, 2011-08-03 at 11:40 -0400, Tom Lane wrote: The other problem is that once autovacuum has gotten the lock, it has to keep it for long enough to re-scan the truncatable pages (to make sure they're still empty). And it is set up so that any access to the table will kick autovacuum off the lock. An access pattern like that would very likely prevent it from ever truncating, if there are a lot of pages that need to be truncated. (There's been some discussion of modifying this behavior, but nothing's been done about it yet.) Michael Graham mgra...@bloxx.com writes: Ah! This looks like it is very much the issue. Since I've got around 150GB of data that should be truncatable and a select every ~2s. Just to confirm would postgres write: 2011-08-03 16:09:55 BST ERROR: canceling autovacuum task 2011-08-03 16:09:55 BST CONTEXT: automatic vacuum of table traffic.public.logdata5queue Under those circumstances? Yup ... If you do a manual VACUUM, it won't allow itself to get kicked off the lock ... but as noted upthread, that will mean your other queries get blocked till it's done. Not sure there's any simple fix for this that doesn't involve some downtime. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [PERFORM] Ineffective autovacuum
Royce Ausburn royce...@inomial.com writes: I have a problem with autovacuum apparently not doing the job I need it to do. Hm, I wonder whether you're getting bit by bug #5759, which was fixed after 8.3.12. I have a table named datasession that is frequently inserted, updated and deleted from. Typically the table will have a few thousand rows in it. Each row typically survives a few days and is updated every 5 - 10 mins. The application receives unreliable, potentially duplicate data from its source, so this table is heavily used for synchronising application threads as well. A typical access pattern is: - tx begin - SELECT FOR UPDATE on a single row - Do some application processing (1 - 100 ms) - Possibly UPDATE the row - tx commit Transactions of that form would not interfere with autovacuum. You'd need something that wants exclusive lock, like a schema change. I've read some recent threads and found a discussion (below) on auto vacuum that mentions auto vacuum will be cancelled when a client requests a lock that auto vacuum is using My questions: 1) Does it look like I'm affected by the same problem as in the below discussion? Not unless you're seeing a lot of canceling autovacuum task messages in the postmaster log. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] postgres constraint triggers
On Sep 26, 2011, at 10:52 AM, Maria L. Wilson wrote: Our first try to solve this problem has been to convert these triggers into a constraint trigger which allows for DEFERRABLE INITIALLY DEFERRED flags. This, we are finding, is forcing the trigger function to run after the triggering transaction is completed. We believe this will fix our locking problem and hopefully speed up our inserts again. Any comments or past experiences would certainly be helpful! My memory is fuzzy but as I recall, a possible downside to using deferred constraints was increased memory usage, though I cannot see how at the moment. Regardless, I think the upshot is that they aren't without their cost but as long as you aren't doing massive transactions that cost is probably one that you can afford to pay without much worry.
Re: [PERFORM] Ineffective autovacuum
On 27/09/2011, at 2:21 PM, Tom Lane wrote: Royce Ausburn royce...@inomial.com writes: I have a problem with autovacuum apparently not doing the job I need it to do. Hm, I wonder whether you're getting bit by bug #5759, which was fixed after 8.3.12. If this were the case, would I see lots of auto vacuum worker processes in ps that are essentially doing nothing because they're sleeping all the time? If so, then I think perhaps not. I have a table named datasession that is frequently inserted, updated and deleted from. Typically the table will have a few thousand rows in it. Each row typically survives a few days and is updated every 5 - 10 mins. The application receives unreliable, potentially duplicate data from its source, so this table is heavily used for synchronising application threads as well. A typical access pattern is: - tx begin - SELECT FOR UPDATE on a single row - Do some application processing (1 - 100 ms) - Possibly UPDATE the row - tx commit Transactions of that form would not interfere with autovacuum. You'd need something that wants exclusive lock, like a schema change. I've read some recent threads and found a discussion (below) on auto vacuum that mentions auto vacuum will be cancelled when a client requests a lock that auto vacuum is using∑ My questions: 1) Does it look like I'm affected by the same problem as in the below discussion? Not unless you're seeing a lot of canceling autovacuum task messages in the postmaster log. Okay - This is not the case. Since sending this first email I've up'd the autovacuum log level and I've noticed that the same tables seem to be auto vacuum'd over and over again… Some of the tables are a bit surprising in that they're updated semi-regularly, but not enough (I'd think) to warrant an autovacuum every few minutes… Is this unusual? Perhaps unrelated: I've done some digging around and happened across a nightly task doing: select pg_stat_reset() on each of the databases in the cluster…. I've no idea why we're doing that (and our usual sysadmin / DBA has resigned, so I doubt I'll ever know). There must have been a reason at the time, but I wonder if this might be interfering with things? At any rate, I think the logs might glean some more interesting information, I'll let it alone for a few hours and hopefully I'll have some more useful information. --Royce -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
