Re: [PERFORM] Index scan cost calculation
> From: Jim Nasby <jim.na...@bluetreble.com> >To: Jeff Janes <jeff.ja...@gmail.com>; Glyn Astill <glynast...@yahoo.co.uk> >Cc: Pgsql-performance <pgsql-performance@postgresql.org> >Sent: Wednesday, 2 December 2015, 22:32 >Subject: Re: [PERFORM] Index scan cost calculation > > >On 11/30/15 5:03 PM, Jeff Janes wrote: >> It thinks the combination of (show, type, best, block) is enough to >> get down to a single row. One index adds "flag" to that (which is not >> useful to the query) and the other adds "row" to that, which is useful >> but the planner doesn't think it is because once you are down to a >> single tuple additional selectivity doesn't help. > >It occurs to me that maybe you could force this behavior by building an >index on a row() instead of on the individual fields. IE: > >CREATE INDEX ... ON( row(show, type, best, block, row) ) > >You would then have to query based on that: > >WHERE row(show, type, best, block, row) = row( 'Trans Siberian >Orchestra', 'Music', true, 1, 1 ) > >You mentioned legacy code which presumably you can't modify to do that, >but maybe there's a way to trick the planner into it with a view... > >CREATE VIEW AS >SELECT r.show, r.type, r..., etc, etc > FROM ( SELECT *, row(show, type, best, block, row) AS r FROM table ) a >; > >When you stick a where clause on that there's a chance it'd get turned >into WHERE row() = row()... but now that I see it I'm probably being >over optimistic about that. You could probably force the issue with an >ON SELECT ON table DO INSTEAD rule, but IIRC those aren't supported. Thanks, interesting idea, but no cigar. For the moment just ensuring the seats_index01 is the last index created seems to suffice, fragile though it is. -- 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] Index scan cost calculation
On 11/30/15 5:03 PM, Jeff Janes wrote: It thinks the combination of (show, type, best, block) is enough to get down to a single row. One index adds "flag" to that (which is not useful to the query) and the other adds "row" to that, which is useful but the planner doesn't think it is because once you are down to a single tuple additional selectivity doesn't help. It occurs to me that maybe you could force this behavior by building an index on a row() instead of on the individual fields. IE: CREATE INDEX ... ON( row(show, type, best, block, row) ) You would then have to query based on that: WHERE row(show, type, best, block, row) = row( 'Trans Siberian Orchestra', 'Music', true, 1, 1 ) You mentioned legacy code which presumably you can't modify to do that, but maybe there's a way to trick the planner into it with a view... CREATE VIEW AS SELECT r.show, r.type, r..., etc, etc FROM ( SELECT *, row(show, type, best, block, row) AS r FROM table ) a ; When you stick a where clause on that there's a chance it'd get turned into WHERE row() = row()... but now that I see it I'm probably being over optimistic about that. You could probably force the issue with an ON SELECT ON table DO INSTEAD rule, but IIRC those aren't supported. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com -- 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] Index scan cost calculation
> >Clauses that can't be used in an "indexable" way are excluded from the >index selectivity, but not from the total query selectivity. > >> Or is it just likely that the selection of the new index is just by chance? > >Bingo. > Got it, thanks! Very much appreciated. Glyn -- 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] Index scan cost calculation
On Mon, Nov 30, 2015 at 6:03 AM, Glyn Astillwrote: > > > > > If I create the index show+best+block+row+seat then the planner appears to > favour that, and all is well. Despite the startup cost estimate being the > same, and total cost being 0.01 higher. This is something I fail to > understand fully. I think usually Index scans that are estimated to be within 1% of each other are considered to be identical. Which one gets chosen then depends on what order they are considered in, which I think is in implementation dependent detail. Usually it is the most recently created one, which would explain why you got the plan switch with the new index. > Tom stated the index choice is due to a selectivity underestimate. I think > this may be because there is actually a correlation between "best"+"block" > and "type", but from Toms reply my understanding was that total selectivity > for the query is calculated as the product of the individual selectivities in > the where clause. I think the problem here is not with total query selectivity estimate, but rather selectivity estimates of the indexes. It thinks the combination of (show, type, best, block) is enough to get down to a single row. One index adds "flag" to that (which is not useful to the query) and the other adds "row" to that, which is useful but the planner doesn't think it is because once you are down to a single tuple additional selectivity doesn't help. > Are particular equality clauses ever excluded from the calculation as a > result of available indexes or otherwise? Clauses that can't be used in an "indexable" way are excluded from the index selectivity, but not from the total query selectivity. > Or is it just likely that the selection of the new index is just by chance? Bingo. Cheers, Jeff -- 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] Index scan cost calculation
> From: Jeff Janes <jeff.ja...@gmail.com> > To: Glyn Astill <glynast...@yahoo.co.uk> > Cc: Pgsql-performance <pgsql-performance@postgresql.org> > Sent: Saturday, 28 November 2015, 19:25 > Subject: Re: [PERFORM] Index scan cost calculation > > > Why does the index seats_index02 exist in the first place? It looks > like an index designed for the benefit of a single query. In which > case, could flag column be moved up front? That should prevent it > from looking falsely enticing. > > A column named "flag" is not usually the type of thing you expect to > see a range query on, so moving it leftward in the index should not be > a problem. > Unfortunately it's not possible to move flag left in this scenario. As you say it's an issue that would not really exist in normal SQL access. The main issue is the way it's required for ordering; The index in question is used by a legacy language that accesses records sequentially as if they were direct from isam files it used historically via a driver. In some cases it steps through records on a particular show+type until a flag changes and carries on unless particular values are seen. If I create the index show+best+block+row+seat then the planner appears to favour that, and all is well. Despite the startup cost estimate being the same, and total cost being 0.01 higher. This is something I fail to understand fully. Tom stated the index choice is due to a selectivity underestimate. I think this may be because there is actually a correlation between "best"+"block" and "type", but from Toms reply my understanding was that total selectivity for the query is calculated as the product of the individual selectivities in the where clause. Are particular equality clauses ever excluded from the calculation as a result of available indexes or otherwise? Or is it just likely that the selection of the new index is just by chance? Either way I my understanding here is definitely lacking. -- 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] Index scan cost calculation
On Thu, Nov 26, 2015 at 8:11 AM, Glyn Astillwrote: > Hi All, > > Using pg 9.4.5 I'm looking at a table set up by a 3rd party application and > trying to figure out why a particular index is being chosen over another for > updates/deletes. > > From what I can see the reason is that plans using either index have the same > exactly the same cost. So rather I'm asking if there's something glaringly > obvious I'm missing, or is there anything I can to to get better estimates. > > The table is as follows and has ~ 50M rows, ~ 4.5GB in size: > > CREATE TABLE tickets.seats > ( > recnum serial NOT NULL, > show numeric(8,0) NOT NULL, > type numeric(4,0) NOT NULL, > block character varying(8) NOT NULL, > "row" numeric(14,0) NOT NULL, > seat numeric(8,0) NOT NULL, > flag character varying(15) NOT NULL, > transno numeric(8,0) NOT NULL, > best numeric(4,0) NOT NULL, > "user" character varying(15) NOT NULL, > "time" numeric(10,0) NOT NULL, > date date NOT NULL, > date_reserved timestamp NOT NULL > ); > > Indexes: > "seats_index01" PRIMARY KEY, btree (show, type, best, block, "row", seat) >// (1094 MB) > "seats_index00" UNIQUE, btree (recnum) > // (2423 MB) > "seats_index02" UNIQUE, btree (show, type, best, block, flag, "row", seat, > recnum) // (2908 MB) Why does the index seats_index02 exist in the first place? It looks like an index designed for the benefit of a single query. In which case, could flag column be moved up front? That should prevent it from looking falsely enticing. A column named "flag" is not usually the type of thing you expect to see a range query on, so moving it leftward in the index should not be a problem. Cheers, Jeff -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Index scan cost calculation
Hi All, Using pg 9.4.5 I'm looking at a table set up by a 3rd party application and trying to figure out why a particular index is being chosen over another for updates/deletes. >From what I can see the reason is that plans using either index have the same >exactly the same cost. So rather I'm asking if there's something glaringly >obvious I'm missing, or is there anything I can to to get better estimates. The table is as follows and has ~ 50M rows, ~ 4.5GB in size: CREATE TABLE tickets.seats ( recnum serial NOT NULL, show numeric(8,0) NOT NULL, type numeric(4,0) NOT NULL, block character varying(8) NOT NULL, "row" numeric(14,0) NOT NULL, seat numeric(8,0) NOT NULL, flag character varying(15) NOT NULL, transno numeric(8,0) NOT NULL, best numeric(4,0) NOT NULL, "user" character varying(15) NOT NULL, "time" numeric(10,0) NOT NULL, date date NOT NULL, date_reserved timestamp NOT NULL ); Indexes: "seats_index01" PRIMARY KEY, btree (show, type, best, block, "row", seat) // (1094 MB) "seats_index00" UNIQUE, btree (recnum) // (2423 MB) "seats_index02" UNIQUE, btree (show, type, best, block, flag, "row", seat, recnum) // (2908 MB) default_statistics target is 100, and the following columns are non-default: attname | attstattarget +--- show | 1000 type | 1000 block| 2000 row| 1000 seat | 1000 flag | 1000 best | 1000 Increasing these further appears to make no noticeable difference. (pg_stats here for these columns here: http://pastebin.com/2WQQec7N) An example query below shows that in some cases the seats_index02 index is being chosen: # analyze verbose seats; INFO: analyzing "tickets.seats" INFO: "seats": scanned 593409 of 593409 pages, containing 50926456 live rows and 349030 dead rows; 60 rows in sample, 50926456 estimated total rows # begin; BEGIN # explain analyze delete from seats where ("show" = 58919 AND "type" = 1 AND "best" = 10 AND "block" = 'GMA' AND "row" =26 AND "seat" = 15); QUERY PLAN - Delete on seats (cost=0.56..4.59 rows=1 width=6) (actual time=0.480..0.480 rows=0 loops=1) -> Index Scan using seats_index02 on seats (cost=0.56..4.59 rows=1 width=6) (actual time=0.452..0.453 rows=1 loops=1) Index Cond: ((show = 58919::numeric) AND (type = 1::numeric) AND (best = 10::numeric) AND ((block)::text = 'GMA'::text) AND ("row" = 26::numeric) AND (seat = 15::numeric)) Planning time: 2.172 ms Execution time: 0.531 ms (5 rows) But from my naive standpoint, seats_index01 is a better candidate: # abort; begin; ROLLBACK BEGIN # update pg_index set indisvalid = false where indexrelid = 'seats_index02'::regclass; # explain analyze delete from seats where ("show" = 58919 AND "type" = 1 AND "best" = 10 AND "block" = 'GMA' AND "row" =26 AND "seat" = 15); QUERY PLAN - Delete on seats (cost=0.56..4.59 rows=1 width=6) (actual time=0.103..0.103 rows=0 loops=1) -> Index Scan using seats_index01 on seats (cost=0.56..4.59 rows=1 width=6) (actual time=0.078..0.080 rows=1 loops=1) Index Cond: ((show = 58919::numeric) AND (type = 1::numeric) AND (best = 10::numeric) AND ((block)::text = 'GMA'::text) AND ("row" = 26::numeric) AND (seat = 15::numeric)) Planning time: 0.535 ms Execution time: 0.146 ms (5 rows) In this instance, the time difference is not huge, however in some seemingly random cases where there are a lot of rows with only the "seat" column differing the choice of seats_index02 is much larger ~ 70ms vs 0.something ms with seats_index01 I suspect some of the seemingly random cases could be where there's been an update, followed by a delete since the last analyze, despite auto analyze running fairly frequently. Any suggestions appreciated. Thanks Glyn -- 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] Index scan cost calculation
- Original Message - > From: Glyn Astill <glynast...@yahoo.co.uk> > To: Pgsql-performance <pgsql-performance@postgresql.org> > Sent: Thursday, 26 November 2015, 16:11 > Subject: [PERFORM] Index scan cost calculation > > Hi All, > > Using pg 9.4.5 I'm looking at a table set up by a 3rd party application and > trying to figure out why a particular index is being chosen over another for > updates/deletes. > > From what I can see the reason is that plans using either index have the same > exactly the same cost. So rather I'm asking if there's something > glaringly obvious I'm missing, or is there anything I can to to get better > estimates. > > The table is as follows and has ~ 50M rows, ~ 4.5GB in size: > > CREATE TABLE tickets.seats > ( > recnum serial NOT NULL, > show numeric(8,0) NOT NULL, > type numeric(4,0) NOT NULL, > block character varying(8) NOT NULL, > "row" numeric(14,0) NOT NULL, > seat numeric(8,0) NOT NULL, > flag character varying(15) NOT NULL, > transno numeric(8,0) NOT NULL, > best numeric(4,0) NOT NULL, > "user" character varying(15) NOT NULL, > "time" numeric(10,0) NOT NULL, > date date NOT NULL, > date_reserved timestamp NOT NULL > ); > > Indexes: > "seats_index01" PRIMARY KEY, btree (show, type, best, block, > "row", seat) // (1094 MB) > "seats_index00" UNIQUE, btree (recnum) > // (2423 MB) > "seats_index02" UNIQUE, btree (show, type, best, block, flag, > "row", seat, recnum) // (2908 MB) > ^^ If those first two sizes look wrong, it's because they are; they should be the other way around. > default_statistics target is 100, and the following columns are non-default: > > attname | attstattarget > +--- > show | 1000 > type | 1000 > block| 2000 > row| 1000 > seat | 1000 > flag | 1000 > best | 1000 > > Increasing these further appears to make no noticeable difference. (pg_stats > here for these columns here: http://pastebin.com/2WQQec7N) > > An example query below shows that in some cases the seats_index02 index is > being > chosen: > > # analyze verbose seats; > INFO: analyzing "tickets.seats" > INFO: "seats": scanned 593409 of 593409 pages, containing 50926456 > live rows and 349030 dead rows; 60 rows in sample, 50926456 estimated > total > rows > > # begin; > BEGIN > # explain analyze delete from seats where ("show" = 58919 AND > "type" = 1 AND "best" = 10 AND "block" = > 'GMA' AND "row" =26 AND "seat" = 15); > QUERY PLAN > - > Delete on seats (cost=0.56..4.59 rows=1 width=6) (actual time=0.480..0.480 > rows=0 loops=1) > -> Index Scan using seats_index02 on seats (cost=0.56..4.59 rows=1 width=6) > (actual time=0.452..0.453 rows=1 loops=1) > Index Cond: ((show = 58919::numeric) AND (type = 1::numeric) AND (best = > 10::numeric) AND ((block)::text = 'GMA'::text) AND ("row" = > 26::numeric) AND (seat = 15::numeric)) > Planning time: 2.172 ms > Execution time: 0.531 ms > (5 rows) > > But from my naive standpoint, seats_index01 is a better candidate: > > # abort; begin; > ROLLBACK > BEGIN > > # update pg_index set indisvalid = false where indexrelid = > 'seats_index02'::regclass; > # explain analyze delete from seats where ("show" = 58919 AND > "type" = 1 AND "best" = 10 AND "block" = > 'GMA' AND "row" =26 AND "seat" = 15); > QUERY PLAN > - > Delete on seats (cost=0.56..4.59 rows=1 width=6) (actual time=0.103..0.103 > rows=0 loops=1) > -> Index Scan using seats_index01 on seats (cost=0.56..4.59 rows=1 width=6) > (actual time=0.078..0.080 rows=1 loops=1) > Index Cond: ((show = 58919::numeric) AND (type = 1::numeric) AND (best = > 10::numeric) AND ((block)::text = 'GMA'::text) AND ("row" = > 26::numeric) AND (seat = 15::numeric)) > Planning time: 0.535 ms > Execution time: 0.146 ms > (5 rows) > > > In this instance, the time difference is not huge, however in some se
Re: [PERFORM] Index scan cost calculation
Glyn Astillwrites: > Using pg 9.4.5 I'm looking at a table set up by a 3rd party application and > trying to figure out why a particular index is being chosen over another for > updates/deletes. > From what I can see the reason is that plans using either index have the same > exactly the same cost. So rather I'm asking if there's something glaringly > obvious I'm missing, or is there anything I can to to get better estimates. I think what's happening is that it's estimating that exactly one index tuple needs to be visited in both cases, so that the cost estimates come out the same. That's correct in the one case but overly optimistic in the other; the misestimate likely is a consequence of the index columns being interdependent. For instance, if "type" can be predicted from the other columns then specifying it isn't really adding anything to the query selectivity, but the planner won't know that. We can conclude from the results you've shown that the planner thinks that show+type+best+block is sufficient to uniquely determine a table entry, which implies that at least some of those columns are strongly correlated with row+seat. The problem will probably go away by itself as your table grows, but if you don't want to wait, you might want to reflect on which of the index columns might be (partially?) functionally dependent on the other columns, and whether you could redesign the key structure to avoid that. 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] Index scan cost calculation
- Original Message - > From: Tom Lane <t...@sss.pgh.pa.us> > To: Glyn Astill <glynast...@yahoo.co.uk> > Cc: Pgsql-performance <pgsql-performance@postgresql.org> > Sent: Thursday, 26 November 2015, 16:44 > Subject: Re: [PERFORM] Index scan cost calculation > > Glyn Astill <glynast...@yahoo.co.uk> writes: >> Using pg 9.4.5 I'm looking at a table set up by a 3rd party application > and trying to figure out why a particular index is being chosen over another > for > updates/deletes. >> From what I can see the reason is that plans using either index have the > same exactly the same cost. So rather I'm asking if there's something > glaringly obvious I'm missing, or is there anything I can to to get better > estimates. > > I think what's happening is that it's estimating that exactly one index > tuple needs to be visited in both cases, so that the cost estimates come > out the same. That's correct in the one case but overly optimistic in the > other; the misestimate likely is a consequence of the index columns being > interdependent. For instance, if "type" can be predicted from the > other > columns then specifying it isn't really adding anything to the query > selectivity, but the planner won't know that. We can conclude from the > results you've shown that the planner thinks that show+type+best+block > is sufficient to uniquely determine a table entry, which implies that > at least some of those columns are strongly correlated with row+seat. > > The problem will probably go away by itself as your table grows, but > if you don't want to wait, you might want to reflect on which of the index > columns might be (partially?) functionally dependent on the other columns, > and whether you could redesign the key structure to avoid that. Many thanks for the explanation, is such a functional dependency assumed purely based optimistically on statistics gathered by analyze? My (ignorant) thinking was that those sorts of decisions would only be made from keys or constraints on the table. There's no way to determine a particular seat+row combination from show+type+best+block or vice versa. We need show+type+best+block+row+seat to identify an individual row, but approximately 90% of the table has just a space " " for the value of "block", and zeros for both "best" and "row", and for each of those you could say any show+type would almost certainly have row+seat combinations of 0+1, 0+2 and so on. Unfortunately it's an unnormalized legacy structure that I can't really change. -- 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] Index scan cost calculation
Glyn Astillwrites: >> From: Tom Lane >> The problem will probably go away by itself as your table grows, but >> if you don't want to wait, you might want to reflect on which of the index >> columns might be (partially?) functionally dependent on the other columns, >> and whether you could redesign the key structure to avoid that. > Many thanks for the explanation, is such a functional dependency assumed > purely based optimistically on statistics gathered by analyze? Well, the point is that the selectivities associated with the individual WHERE clauses are assumed independent, which allows us to just multiply them together. If they're not really independent then multiplication gives a combined selectivity that's too small. But without cross-column statistics there's not much we can do to get a better estimate. 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] Index Scan Backward Slow
On 01.05.2015 13:06, David Osborne wrote: Simple... that did it... thanks! dev= create index on table(code,row_id); CREATE INDEX Time: 38088.482 ms dev= explain (analyse,buffers) select row_id as last_row_id from table where code='XX' order by row_id desc limit 1; Just out of curiosity: Is there a particular reason why you do not use select max(row_id) as last_row_id from table where code='XX' ? Kind regards robert -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Index Scan Backward Slow
Hi, We have a query which finds the latest row_id for a particular code. We've found a backwards index scan is much slower than a forward one, to the extent that disabling indexscan altogether actually improves the query time. Can anyone suggest why this might be, and what's best to do to improve the query time? dev= \d table Table public.table Column| Type | Modifiers --++--- row_id | integer| code | character(2) | Indexes: table_code_idx btree (code) table_row_idx btree (row_id) dev= select count(*) from table; count - 6090254 (1 row) dev= select count(distinct(row_id)) from table; count - 5421022 (1 row) dev= select n_distinct from pg_stats where tablename='table' and attname='row_id'; n_distinct -0.762951 (1 row) dev= show work_mem; work_mem --- 1249105kB (1 row) dev= select version(); version -- PostgreSQL 9.3.6 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.6.3 20120306 (Red Hat 4.6.3-2), 64-bit (1 row) The query in question: dev= explain (analyse,buffers) select row_id as last_row_id from table where code='XX' order by row_id desc limit 1; QUERY PLAN -- Limit (cost=0.43..1.67 rows=1 width=4) (actual time=835.281..835.282 rows=1 loops=1) Buffers: shared hit=187961 - Index Scan Backward using table_row_idx on table (cost=0.43..343741.98 rows=278731 width=4) (actual time=835.278..835.278 rows=1 loops=1) Filter: (code = 'XX'::bpchar) Rows Removed by Filter: 4050971 Buffers: shared hit=187961 Total runtime: 835.315 ms (7 rows) http://explain.depesz.com/s/uGC So we can see it's doing a backwards index scan. Out of curiosity I tried a forward scan and it was MUCH quicker: dev= explain (analyse,buffers) select row_id as first_row_id from table where code='XX' order by row_id asc limit 1; QUERY PLAN --- Limit (cost=0.43..1.67 rows=1 width=4) (actual time=19.473..19.474 rows=1 loops=1) Buffers: shared hit=26730 - Index Scan using table_row_idx on table (cost=0.43..343741.98 rows=278731 width=4) (actual time=19.470..19.470 rows=1 loops=1) Filter: (code = 'XX'::bpchar) Rows Removed by Filter: 62786 Buffers: shared hit=26730 Total runtime: 19.509 ms (7 rows) http://explain.depesz.com/s/ASxD I thought adding a index on row_id desc might be the answer but it has little effect: dev= create index row_id_desc_idx on table(row_id desc); CREATE INDEX Time: 5293.812 ms dev= explain (analyse,buffers) select row_id as last_row_id from table where code='XX' order by row_id desc limit 1; QUERY PLAN Limit (cost=0.43..1.66 rows=1 width=4) (actual time=944.666..944.667 rows=1 loops=1) Buffers: shared hit=176711 read=11071 - Index Scan using row_id_desc_idx on table (cost=0.43..342101.98 rows=278731 width=4) (actual time=944.663..944.663 rows=1 loops=1) Filter: (code = 'XX'::bpchar) Rows Removed by Filter: 4050971 Buffers: shared hit=176711 read=11071 Total runtime: 944.699 ms (7 rows) http://explain.depesz.com/s/JStM In fact, disabling the index scan completely improves matters considerably: dev= drop index row_id_desc_idx; DROP INDEX dev= set enable_indexscan to off; SET dev= explain (analyse,buffers) select row_id as last_row_id from table where code='XX' order by row_id desc limit 1; QUERY PLAN Limit (cost=74006.39..74006.39 rows=1 width=4) (actual time=183.997..183.998 rows=1 loops=1) Buffers: shared hit=14723 - Sort (cost=74006.39..74703.22 rows=278731 width=4) (actual time=183.995..183.995 rows=1 loops=1) Sort Key: row_id Sort Method: top-N heapsort Memory: 25kB Buffers: shared hit=14723 - Bitmap Heap Scan on table (cost=5276.60..72612.74 rows=278731 width=4) (actual time=25.533..119.320 rows=275909 loops=1) Recheck Cond: (code = 'XX'::bpchar) Buffers: shared hit=14723 - Bitmap Index Scan on table_code_idx (cost=0.00..5206.91
Re: [PERFORM] Index Scan Backward Slow
Simple... that did it... thanks! dev= create index on table(code,row_id); CREATE INDEX Time: 38088.482 ms dev= explain (analyse,buffers) select row_id as last_row_id from table where code='XX' order by row_id desc limit 1; QUERY PLAN Limit (cost=0.43..0.46 rows=1 width=4) (actual time=0.070..0.071 rows=1 loops=1) Buffers: shared hit=2 read=3 - Index Only Scan Backward using table_code_row_id_idx on table (cost=0.43..7999.28 rows=278743 width=4) (actual time=0.067..0.067 rows=1 loops=1) Index Cond: (code = 'XX'::bpchar) Heap Fetches: 1 Buffers: shared hit=2 read=3 Total runtime: 0.097 ms (7 rows) On 1 May 2015 at 11:59, Evgeniy Shishkin itparan...@gmail.com wrote: On 01 May 2015, at 13:54, David Osborne da...@qcode.co.uk wrote: Hi, We have a query which finds the latest row_id for a particular code. We've found a backwards index scan is much slower than a forward one, to the extent that disabling indexscan altogether actually improves the query time. Can anyone suggest why this might be, and what's best to do to improve the query time? dev= \d table Table public.table Column| Type | Modifiers --++--- row_id | integer| code | character(2) | Indexes: table_code_idx btree (code) table_row_idx btree (row_id) dev= select count(*) from table; count - 6090254 (1 row) dev= select count(distinct(row_id)) from table; count - 5421022 (1 row) dev= select n_distinct from pg_stats where tablename='table' and attname='row_id'; n_distinct -0.762951 (1 row) dev= show work_mem; work_mem --- 1249105kB (1 row) dev= select version(); version -- PostgreSQL 9.3.6 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.6.3 20120306 (Red Hat 4.6.3-2), 64-bit (1 row) The query in question: dev= explain (analyse,buffers) select row_id as last_row_id from table where code='XX' order by row_id desc limit 1; QUERY PLAN -- Limit (cost=0.43..1.67 rows=1 width=4) (actual time=835.281..835.282 rows=1 loops=1) Buffers: shared hit=187961 - Index Scan Backward using table_row_idx on table (cost=0.43..343741.98 rows=278731 width=4) (actual time=835.278..835.278 rows=1 loops=1) Filter: (code = 'XX'::bpchar) Rows Removed by Filter: 4050971 Buffers: shared hit=187961 Total runtime: 835.315 ms (7 rows) http://explain.depesz.com/s/uGC So we can see it's doing a backwards index scan. Out of curiosity I tried a forward scan and it was MUCH quicker: dev= explain (analyse,buffers) select row_id as first_row_id from table where code='XX' order by row_id asc limit 1; QUERY PLAN --- Limit (cost=0.43..1.67 rows=1 width=4) (actual time=19.473..19.474 rows=1 loops=1) Buffers: shared hit=26730 - Index Scan using table_row_idx on table (cost=0.43..343741.98 rows=278731 width=4) (actual time=19.470..19.470 rows=1 loops=1) Filter: (code = 'XX'::bpchar) Rows Removed by Filter: 62786 Buffers: shared hit=26730 Total runtime: 19.509 ms (7 rows) http://explain.depesz.com/s/ASxD I thought adding a index on row_id desc might be the answer but it has little effect: dev= create index row_id_desc_idx on table(row_id desc); CREATE INDEX Time: 5293.812 ms dev= explain (analyse,buffers) select row_id as last_row_id from table where code='XX' order by row_id desc limit 1; QUERY PLAN Limit (cost=0.43..1.66 rows=1 width=4) (actual time=944.666..944.667 rows=1 loops=1) Buffers: shared hit=176711 read=11071 - Index Scan using row_id_desc_idx on table (cost=0.43..342101.98 rows=278731 width=4) (actual time=944.663..944.663 rows=1 loops=1) Filter: (code = 'XX'::bpchar) Rows Removed by Filter: 4050971 Buffers: shared hit=176711 read=11071 Total runtime: 944.699 ms (7 rows)
Re: [PERFORM] Index Scan Backward Slow
On 01 May 2015, at 13:54, David Osborne da...@qcode.co.uk wrote: Hi, We have a query which finds the latest row_id for a particular code. We've found a backwards index scan is much slower than a forward one, to the extent that disabling indexscan altogether actually improves the query time. Can anyone suggest why this might be, and what's best to do to improve the query time? dev= \d table Table public.table Column| Type | Modifiers --++--- row_id | integer| code | character(2) | Indexes: table_code_idx btree (code) table_row_idx btree (row_id) dev= select count(*) from table; count - 6090254 (1 row) dev= select count(distinct(row_id)) from table; count - 5421022 (1 row) dev= select n_distinct from pg_stats where tablename='table' and attname='row_id'; n_distinct -0.762951 (1 row) dev= show work_mem; work_mem --- 1249105kB (1 row) dev= select version(); version -- PostgreSQL 9.3.6 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.6.3 20120306 (Red Hat 4.6.3-2), 64-bit (1 row) The query in question: dev= explain (analyse,buffers) select row_id as last_row_id from table where code='XX' order by row_id desc limit 1; QUERY PLAN -- Limit (cost=0.43..1.67 rows=1 width=4) (actual time=835.281..835.282 rows=1 loops=1) Buffers: shared hit=187961 - Index Scan Backward using table_row_idx on table (cost=0.43..343741.98 rows=278731 width=4) (actual time=835.278..835.278 rows=1 loops=1) Filter: (code = 'XX'::bpchar) Rows Removed by Filter: 4050971 Buffers: shared hit=187961 Total runtime: 835.315 ms (7 rows) http://explain.depesz.com/s/uGC So we can see it's doing a backwards index scan. Out of curiosity I tried a forward scan and it was MUCH quicker: dev= explain (analyse,buffers) select row_id as first_row_id from table where code='XX' order by row_id asc limit 1; QUERY PLAN --- Limit (cost=0.43..1.67 rows=1 width=4) (actual time=19.473..19.474 rows=1 loops=1) Buffers: shared hit=26730 - Index Scan using table_row_idx on table (cost=0.43..343741.98 rows=278731 width=4) (actual time=19.470..19.470 rows=1 loops=1) Filter: (code = 'XX'::bpchar) Rows Removed by Filter: 62786 Buffers: shared hit=26730 Total runtime: 19.509 ms (7 rows) http://explain.depesz.com/s/ASxD I thought adding a index on row_id desc might be the answer but it has little effect: dev= create index row_id_desc_idx on table(row_id desc); CREATE INDEX Time: 5293.812 ms dev= explain (analyse,buffers) select row_id as last_row_id from table where code='XX' order by row_id desc limit 1; QUERY PLAN Limit (cost=0.43..1.66 rows=1 width=4) (actual time=944.666..944.667 rows=1 loops=1) Buffers: shared hit=176711 read=11071 - Index Scan using row_id_desc_idx on table (cost=0.43..342101.98 rows=278731 width=4) (actual time=944.663..944.663 rows=1 loops=1) Filter: (code = 'XX'::bpchar) Rows Removed by Filter: 4050971 Buffers: shared hit=176711 read=11071 Total runtime: 944.699 ms (7 rows) http://explain.depesz.com/s/JStM In fact, disabling the index scan completely improves matters considerably: dev= drop index row_id_desc_idx; DROP INDEX dev= set enable_indexscan to off; SET dev= explain (analyse,buffers) select row_id as last_row_id from table where code='XX' order by row_id desc limit 1; QUERY PLAN
Re: [PERFORM] index scan forward vs backward = speed difference of 357X slower!
Kevin Traster ktras...@freshgrillfoods.com writes: The query plan and estimates are exactly the same, except desc has index scan backwards instead of index scan for changes_shareschange. Yet, actual runtime performance is different by 357x slower for the ascending version instead of descending. Apparently, there are some rows passing the filter condition that are close to the end of the index, but none that are close to the start. So it takes a lot longer to find the first 15 matches in one case than the other. You haven't shown us the index definition, but I gather from the fact that the scan condition is just a Filter (not an Index Cond) that the index itself doesn't offer any clue as to whether a given row meets those conditions. So this plan is going to be doing a lot of random-access heap probes until it finds a match. Why and how do I fix it? Probably, you need an index better suited to the query condition. If you have one and the problem is that the planner's not choosing it, then this is going to take more information to resolve. 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] index scan forward vs backward = speed difference of 357X slower!
Typo: Work_mem = 32 MB The definition for both column and index: shareschange | numeric | changes_shareschange btree (shareschange) Index created using: CREATE INDEX changes_shareschange ON changes(shareschange); The entire table is created nightly (and analyzed afterwords), and used only for reporting - there no updates/deletes, so there shouldn't be any dead rows in the table. Likewise, there is no nulls in the column. Please elaborate on: You haven't shown us the index definition, but I gather from the fact that the scan condition is just a Filter (not an Index Cond) that the index itself doesn't offer any clue as to whether a given row meets those conditions Are you saying it is the retrieval of the physically random located 15 rows to meet the ascending condition that causes the 5 sec difference? The table is not-clustered, so it is random for descending also. The condition is shareschange ascending, I have an index for that condition and the planner is using it. What else can I look at? On Wed, Feb 8, 2012 at 11:31 AM, Tom Lane t...@sss.pgh.pa.us wrote: Kevin Traster ktras...@freshgrillfoods.com writes: The query plan and estimates are exactly the same, except desc has index scan backwards instead of index scan for changes_shareschange. Yet, actual runtime performance is different by 357x slower for the ascending version instead of descending. Apparently, there are some rows passing the filter condition that are close to the end of the index, but none that are close to the start. So it takes a lot longer to find the first 15 matches in one case than the other. You haven't shown us the index definition, but I gather from the fact that the scan condition is just a Filter (not an Index Cond) that the index itself doesn't offer any clue as to whether a given row meets those conditions. So this plan is going to be doing a lot of random-access heap probes until it finds a match. Why and how do I fix it? Probably, you need an index better suited to the query condition. If you have one and the problem is that the planner's not choosing it, then this is going to take more information to resolve. 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] index scan forward vs backward = speed difference of 357X slower!
On Wed, Feb 8, 2012 at 1:58 PM, Kevin Traster ktras...@freshgrillfoods.com wrote: Typo: Work_mem = 32 MB The definition for both column and index: shareschange | numeric | changes_shareschange btree (shareschange) Index created using: CREATE INDEX changes_shareschange ON changes(shareschange); The entire table is created nightly (and analyzed afterwords), and used only for reporting - there no updates/deletes, so there shouldn't be any dead rows in the table. Likewise, there is no nulls in the column. Please elaborate on: You haven't shown us the index definition, but I gather from the fact that the scan condition is just a Filter (not an Index Cond) that the index itself doesn't offer any clue as to whether a given row meets those conditions Are you saying it is the retrieval of the physically random located 15 rows to meet the ascending condition that causes the 5 sec difference? The table is not-clustered, so it is random for descending also. The condition is shareschange ascending, I have an index for that condition and the planner is using it. This is not a problem with dead rows, but the index is not really satisfying your query and the database has to look through an indeterminate amount of rows until the 'limit 15' is satisfied. Yeah, backwards scans are slower, especially for disk bound scans but you also have to consider how many filter misses your have. The smoking gun is here: Index Scan Backward using changes_shareschange on changes (cost=0.00..925150.26 rows=181997 width=98) (actual time=3.161..15.843 rows=15 loops=1) Filter: ((activity = ANY ('{4,5}'::integer[])) AND (mfiled = $1)) When you see Filter: xyz, xyz is what each record has to be compared against after the index pointed you to an area(s) in the heap. It's pure luck going forwards or backwards that determines how many records you have to look through to get 15 good ones as defined by satisfying the filter. To prove that one way or the other you can convert your where to a boolean returning (and bump the limit appropriately) expression to see how many records get filtered out. merlin -- 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] index scan forward vs backward = speed difference of 357X slower!
This is not a problem with dead rows, but the index is not really satisfying your query and the database has to look through an indeterminate amount of rows until the 'limit 15' is satisfied. Yeah, backwards scans are slower, especially for disk bound scans but you also have to consider how many filter misses your have. The smoking gun is here: Index Scan Backward using changes_shareschange on changes (cost=0.00..925150.26 rows=181997 width=98) (actual time=3.161..15.843 rows=15 loops=1) Filter: ((activity = ANY ('{4,5}'::integer[])) AND (mfiled = $1)) When you see Filter: xyz, xyz is what each record has to be compared against after the index pointed you to an area(s) in the heap. It's pure luck going forwards or backwards that determines how many records you have to look through to get 15 good ones as defined by satisfying the filter. To prove that one way or the other you can convert your where to a boolean returning (and bump the limit appropriately) expression to see how many records get filtered out. merlin I have indexes also on activity and mfiled (both btree) - wouldn't the database use them? - Kevin -- 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] index scan forward vs backward = speed difference of 357X slower!
Kevin Traster ktras...@freshgrillfoods.com wrote: I have indexes also on activity and mfiled (both btree) - wouldn't the database use them? - Kevin It will use them if they are part of the plan which had the lowest cost when it compared the costs of all possible plans. You haven't really shown us the schema, so there's more guesswork involved in trying to help you than there could be. This page might be worth reviewing: http://wiki.postgresql.org/wiki/SlowQueryQuestions In particular, if there are indexes that aren't being used which you think should be, there is a good chance that either there is a type mismatch or your costing factors may need adjustment. -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] index scan forward vs backward = speed difference of 357X slower!
PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-51), 64-bit Dedicated DB server 4GB ram Shared_Buffers = 1 GB Effective_cache_size = 3GB Work_mem = 32GB Analyze done Queries ran multiple times, same differences/results Default Statistics = 1000 Query (5366ms) : explain analyze select initcap (fullname), initcap(issuer),upper(rsymbol), initcap(industry), activity,to_char(shareschange,'FM9,999,999,999,999,999'),sharespchange ||+ E'\%' from changes where activity in (4,5) and mfiled = (select max(mfiled) from changes) order by shareschange asc limit 15 Slow Ascending explain Analyze: http://explain.depesz.com/s/zFz Query (15ms) : explain analyze select initcap (fullname), initcap(issuer),upper(rsymbol), initcap(industry), activity,to_char(shareschange,'FM9,999,999,999,999,999'),sharespchange ||+ E'\%' from changes where activity in (4,5) and mfiled = (select max(mfiled) from changes) order by shareschange desc limit 15 Fast descending explain analyze: http://explain.depesz.com/s/OP7 The index: changes_shareschange is a btree index created with default ascending order The query plan and estimates are exactly the same, except desc has index scan backwards instead of index scan for changes_shareschange. Yet, actual runtime performance is different by 357x slower for the ascending version instead of descending. Why and how do I fix it?
Re: [PERFORM] Index scan is not working, why??
please provide non-default config options on this host plus the same from a host which is using an index scan, please. Also, postgresql version, OS, and all of the other stuff that is asked for in this document: http://wiki.postgresql.org/wiki/SlowQueryQuestions. It is impossible to say why the query planner might be choosing a particular plan without any insight whatsoever as to how the server is configured. On Wed, Oct 20, 2010 at 10:25 PM, AI Rumman rumman...@gmail.com wrote: I don't know why seq scan is running on the following query where the same query is giving index scan on other servers: explain analyze select * from act where act.acttype in ( 'Meeting','Call','Task'); QUERY PLAN Seq Scan on act (cost=0.00..13386.78 rows=259671 width=142) (actual time=0.013..484.572 rows=263639 loops=1) Filter: (((acttype)::text = 'Meeting'::text) OR ((acttype)::text = 'Call'::text) OR ((acttype)::text = 'Task'::text)) Total runtime: 732.956 ms (3 rows) The above query is giving index scan on other servers and even if I rewrite the query as follows I got index scan: explain analyze select * from act where act.acttype = 'Meeting' or act.acttype = 'Call'; QUERY PLAN -- Bitmap Heap Scan on act (cost=17.98..1083.80 rows=2277 width=142) (actual time=1.901..9.722 rows=4808 loops=1) Recheck Cond: (((acttype)::text = 'Meeting'::text) OR ((acttype)::text = 'Call'::text)) - BitmapOr (cost=17.98..17.98 rows=2281 width=0) (actual time=1.262..1.262 rows=0 loops=1) - Bitmap Index Scan on act_acttype_idx (cost=0.00..8.99 rows=1141 width=0) (actual time=0.790..0.790 rows=3181 loops=1) Index Cond: ((acttype)::text = 'Meeting'::text) - Bitmap Index Scan on act_acttype_idx (cost=0.00..8.99 rows=1141 width=0) (actual time=0.469..0.469 rows=1630 loops=1) Index Cond: ((acttype)::text = 'Call'::text) Total runtime: 14.227 ms (8 rows) \d act Table public.act Column | Type | Modifiers --++--- actid | integer | not null default 0 subject | character varying(250) | not null semodule | character varying(20) | acttype | character varying(200) | not null date_start | date | not null due_date | date | time_start | character varying(50) | time_end | character varying(50) | sendnotification | character varying(3) | not null default '0'::character varying duration_hours | character varying(2) | duration_minutes | character varying(200) | status | character varying(200) | eventstatus | character varying(200) | priority | character varying(200) | location | character varying(150) | notime | character varying(3) | not null default '0'::character varying visibility | character varying(50) | not null default 'all'::character varying recurringtype | character varying(200) | end_date | date | end_time | character varying(50) | Indexes: act_pkey PRIMARY KEY, btree (actid) act_acttype_idx btree (acttype) act_date_start_idx btree (date_start) act_due_date_idx btree (due_date) act_eventstatus_idx btree (eventstatus) act_status_idx btree (status) act_subject_idx btree (subject) act_time_start_idx btree (time_start) Any idea please.
Re: [PERFORM] Index scan is not working, why??
On Thu, Oct 21, 2010 at 1:51 AM, Samuel Gendler sgend...@ideasculptor.com wrote: please provide non-default config options on this host plus the same from a host which is using an index scan, please. Also, postgresql version, OS, and all of the other stuff that is asked for in this document: http://wiki.postgresql.org/wiki/SlowQueryQuestions. It is impossible to say why the query planner might be choosing a particular plan without any insight whatsoever as to how the server is configured. I know it's mentioned in that wiki doc, but the ddl for the table and its indexes, or the output of \d tablename is quite useful and should be included as well. -- 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] Index scan is not working, why??
AI Rumman wrote: I don't know why seq scan is running on the following query where the same query is giving index scan on other servers: explain analyze select * from act where act.acttype in ( 'Meeting','Call','Task'); QUERY PLAN Seq Scan on act (cost=0.00..13386.78 rows=259671 width=142) (actual time=0.013..484.572 rows=263639 loops=1) Filter: (((acttype)::text = 'Meeting'::text) OR ((acttype)::text = 'Call'::text) OR ((acttype)::text = 'Task'::text)) Total runtime: 732.956 ms (3 rows) Al, what percentage of the rows fits the above criteria? How big are your histograms? -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 www.vmsinfo.com -- 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] Index scan is not working, why??
-Original Message- From: AI Rumman [mailto:rumman...@gmail.com] Sent: Thursday, October 21, 2010 1:25 AM To: pgsql-performance@postgresql.org Subject: Index scan is not working, why?? I don't know why seq scan is running on the following query where the same query is giving index scan on other servers: explain analyze select * from act where act.acttype in ( 'Meeting','Call','Task'); QUERY PLAN -- -- Seq Scan on act (cost=0.00..13386.78 rows=259671 width=142) (actual time=0.013..484.572 rows=263639 loops=1) Filter: (((acttype)::text = 'Meeting'::text) OR ((acttype)::text = 'Call'::text) OR ((acttype)::text = 'Task'::text)) Total runtime: 732.956 ms (3 rows) The above query is giving index scan on other servers and even if I rewrite the query as follows I got index scan: explain analyze select * from act where act.acttype = 'Meeting' or act.acttype = 'Call'; QUERY PLAN -- -- -- Bitmap Heap Scan on act (cost=17.98..1083.80 rows=2277 width=142) (actual time=1.901..9.722 rows=4808 loops=1) Recheck Cond: (((acttype)::text = 'Meeting'::text) OR ((acttype)::text = 'Call'::text)) - BitmapOr (cost=17.98..17.98 rows=2281 width=0) (actual time=1.262..1.262 rows=0 loops=1) - Bitmap Index Scan on act_acttype_idx (cost=0.00..8.99 rows=1141 width=0) (actual time=0.790..0.790 rows=3181 loops=1) Index Cond: ((acttype)::text = 'Meeting'::text) - Bitmap Index Scan on act_acttype_idx (cost=0.00..8.99 rows=1141 width=0) (actual time=0.469..0.469 rows=1630 loops=1) Index Cond: ((acttype)::text = 'Call'::text) Total runtime: 14.227 ms (8 rows) Index Scan is not alwayes prefarable to Seq Scan, it depends on selectivity of your query. When retrieving substancial portion of big table seq scan is usually faster, that's why optimizer chooses it. Your queries (and possibly data sets in the tables on different servers) are not the same. Your first query (which uses seq scan) returns 259671 which is probably substantial part of the whole table. Your second query (which uses index scan) returns only 4808 rows, which makes index access less costly in this case. Regards, Igor Neyman -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Index scan is not working, why??
I don't know why seq scan is running on the following query where the same query is giving index scan on other servers: explain analyze select * from act where act.acttype in ( 'Meeting','Call','Task'); QUERY PLAN Seq Scan on act (cost=0.00..13386.78 rows=259671 width=142) (actual time=0.013..484.572 rows=263639 loops=1) Filter: (((acttype)::text = 'Meeting'::text) OR ((acttype)::text = 'Call'::text) OR ((acttype)::text = 'Task'::text)) Total runtime: 732.956 ms (3 rows) The above query is giving index scan on other servers and even if I rewrite the query as follows I got index scan: explain analyze select * from act where act.acttype = 'Meeting' or act.acttype = 'Call'; QUERY PLAN -- Bitmap Heap Scan on act (cost=17.98..1083.80 rows=2277 width=142) (actual time=1.901..9.722 rows=4808 loops=1) Recheck Cond: (((acttype)::text = 'Meeting'::text) OR ((acttype)::text = 'Call'::text)) - BitmapOr (cost=17.98..17.98 rows=2281 width=0) (actual time=1.262..1.262 rows=0 loops=1) - Bitmap Index Scan on act_acttype_idx (cost=0.00..8.99 rows=1141 width=0) (actual time=0.790..0.790 rows=3181 loops=1) Index Cond: ((acttype)::text = 'Meeting'::text) - Bitmap Index Scan on act_acttype_idx (cost=0.00..8.99 rows=1141 width=0) (actual time=0.469..0.469 rows=1630 loops=1) Index Cond: ((acttype)::text = 'Call'::text) Total runtime: 14.227 ms (8 rows) \d act Table public.act Column | Type | Modifiers --++--- actid | integer | not null default 0 subject | character varying(250) | not null semodule | character varying(20) | acttype | character varying(200) | not null date_start | date | not null due_date | date | time_start | character varying(50) | time_end | character varying(50) | sendnotification | character varying(3) | not null default '0'::character varying duration_hours | character varying(2) | duration_minutes | character varying(200) | status | character varying(200) | eventstatus | character varying(200) | priority | character varying(200) | location | character varying(150) | notime | character varying(3) | not null default '0'::character varying visibility | character varying(50) | not null default 'all'::character varying recurringtype | character varying(200) | end_date | date | end_time | character varying(50) | Indexes: act_pkey PRIMARY KEY, btree (actid) act_acttype_idx btree (acttype) act_date_start_idx btree (date_start) act_due_date_idx btree (due_date) act_eventstatus_idx btree (eventstatus) act_status_idx btree (status) act_subject_idx btree (subject) act_time_start_idx btree (time_start) Any idea please.
Re: [PERFORM] Index scan / Index cond limitation or ?
15.10.2010 22:29, Tom Lane: Nikolai Zhubrn-a-zh...@yandex.ru writes: So, in (1, 2) condition is not in Index Cond anymore! Why is that? How can I push it back? It thinks the indexscan condition is sufficiently selective already. An = ANY condition like that will force multiple index searches, one for each of the OR'd possibilities, so it's far from free to add it to the index condition. The planner doesn't think it's worth it. Aha, ok. It makes sense then. Is this specific case (=ANY in index cond) described somewhere with reasonable detail? I always try to RTFM first and most of the time I can find pretty good hints in the regular manual already (sufficient as a starting point at least) but this specific topic seems to be somewhat mysterious. Perhaps on your real query it is, but there's not much point in debating about the behavior on this toy table; without realistic table sizes and up-to-date stats it's impossible to say whether that choice is correct or not. SELECT version(); PostgreSQL 8.3.1, compiled by Visual C++ build 1400 You really, really, really ought to be running 8.3.something-newer. We didn't put out the last 11 8.3.x bugfix updates just because we didn't have anything better to do. Yes, I know, and I do appreciate the efforts of postgresql devels to create updates for older versions too. This server is internal-only (so it does not see any real world yet). Anyway, I hope to update everything to 9.0.1 soon. Thank you! Nikolai 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
[PERFORM] Index scan / Index cond limitation or ?
Hello people, I'm having trouble to persuade index scan to check all of the conditions I specify _inside_ index cond. That is, _some_ condition always get pushed out of index cond and applied later (which will often result, for my real table contents, in too many unwanted rows initially hit by index scan and hence randomly slow queries) An index with all relevant columns does exist of course. Here goes an example. create table foo ( id serial primary key, rec_time timestamp with time zone DEFAULT now(), some_value integer, some_data text ); CREATE INDEX foo_test ON foo (id, rec_time, some_value); set enable_seqscan = false; set enable_bitmapscan = true; explain select id from foo where true and rec_time '2010-01-01 22:00:06' --and rec_time '2010-10-14 23:59' and some_value in (1, 2) and id 123 This one works perfectly as I want it (and note and rec_time ... condition is commented out): Bitmap Heap Scan on foo (cost=13.18..17.19 rows=1 width=4) Recheck Cond: ((id 123) AND (rec_time '2010-01-01 22:00:06+03'::timestamp with time zone) AND (some_value = ANY ('{1,2}'::integer[]))) - Bitmap Index Scan on foo_test (cost=0.00..13.18 rows=1 width=0) Index Cond: ((id 123) AND (rec_time '2010-01-01 22:00:06+03'::timestamp with time zone) AND (some_value = ANY ('{1,2}'::integer[]))) Now, as soon as I enable and rec_time ... condition, I get the following: explain select id from foo where true and rec_time '2010-01-01 22:00:06' and rec_time '2010-10-14 23:59' and some_value in (1, 2) and id 123 Bitmap Heap Scan on foo (cost=8.59..13.94 rows=1 width=4) Recheck Cond: ((id 123) AND (rec_time '2010-01-01 22:00:06+03'::timestamp with time zone) AND (rec_time '2010-10-14 23:59:00+04'::timestamp with time zone)) Filter: (some_value = ANY ('{1,2}'::integer[])) - Bitmap Index Scan on foo_test (cost=0.00..8.59 rows=2 width=0) Index Cond: ((id 123) AND (rec_time '2010-01-01 22:00:06+03'::timestamp with time zone) AND (rec_time '2010-10-14 23:59:00+04'::timestamp with time zone)) So, in (1, 2) condition is not in Index Cond anymore! Why is that? How can I push it back? SELECT version(); PostgreSQL 8.3.1, compiled by Visual C++ build 1400 but the behaviour seems exactly the same in 9.0 (just checked it briefly). Thank you! Please CC me, I'm not on the list. Nikolai -- 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] Index scan / Index cond limitation or ?
Nikolai Zhubr n-a-zh...@yandex.ru writes: So, in (1, 2) condition is not in Index Cond anymore! Why is that? How can I push it back? It thinks the indexscan condition is sufficiently selective already. An = ANY condition like that will force multiple index searches, one for each of the OR'd possibilities, so it's far from free to add it to the index condition. The planner doesn't think it's worth it. Perhaps on your real query it is, but there's not much point in debating about the behavior on this toy table; without realistic table sizes and up-to-date stats it's impossible to say whether that choice is correct or not. SELECT version(); PostgreSQL 8.3.1, compiled by Visual C++ build 1400 You really, really, really ought to be running 8.3.something-newer. We didn't put out the last 11 8.3.x bugfix updates just because we didn't have anything better to do. 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] Index Scan taking long time
Hi Tom, We have managed to improve significantly on the speed of this query. The way that we did this was through clustering the table based on the domain index which significantly reduced the page reads that were required in order to perform the query. Also to find this we turned on log_statement_stats to see what it was doing. This was on a table of roughly 600MB where the domains were randomly dispersed. Cheers Bryce Tom Lane wrote: Bryce Ewing br...@smx.co.nz writes: So it seems to me that once the index is in memory everything is fine with the world, but the loading of the index into memory is horrendous. So it would seem. What's the disk hardware on this machine? It's possible that part of the problem is table bloat, leading to the indexscan having to fetch many more pages than it would if the table were more compact. 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] Index Scan taking long time
Scott Marlowe scott.marl...@gmail.com writes: Without looking at the explain just yet, it seems to me that you are constraining the order of joins to insist that the left joins be done first, then the regular joins second, because of your mix of explicit and implicit join syntax. The query planner is constrained to run explicit joins first, then implicit if I remember correctly. That isn't true as of recent releases (8.2 and up, I think). It is true that there are semantic constraints that prevent certain combinations of inner and outer joins from being rearranged ... but if that applies here, it would also prevent manual rearrangement, unless the OP decides that this query doesn't express quite what he meant. 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] Index Scan taking long time
The nested loops (which are due to the joins) don't seem to be part of the problem at all. The main time that is taken (actual time that is) is in this part: Index Scan using event_20090526_domain_idx on event_20090526 e (cost=0.00..10694.13 rows=3606 width=1276) (actual time=50.233..14305.211 rows=3453 loops=1) Index Cond: (e.domain_id = d.id) Which is the leaf node in the query plan, the total time for the query being: Total runtime: 14380.000 ms And as I said once that query is run once it then does the same query plan and has this output for the same leaf node above: Index Scan using event_20090526_domain_idx on event_20090526 e (cost=0.00..10694.13 rows=3606 width=1276) (actual time=0.027..7.510 rows=3453 loops=1) Index Cond: (e.domain_id = d.id) So it seems to me that once the index is in memory everything is fine with the world, but the loading of the index into memory is horrendous. Tom Lane wrote: Scott Marlowe scott.marl...@gmail.com writes: Without looking at the explain just yet, it seems to me that you are constraining the order of joins to insist that the left joins be done first, then the regular joins second, because of your mix of explicit and implicit join syntax. The query planner is constrained to run explicit joins first, then implicit if I remember correctly. That isn't true as of recent releases (8.2 and up, I think). It is true that there are semantic constraints that prevent certain combinations of inner and outer joins from being rearranged ... but if that applies here, it would also prevent manual rearrangement, unless the OP decides that this query doesn't express quite what he meant. regards, tom lane -- *Bryce Ewing *| Platform Architect *DDI:* +64 9 950 2195 *Fax:* +64 9 302 0518 *Mobile:* +64 21 432 293 *Freephone:* 0800 SMX SMX (769 769) Level 11, 290 Queen Street, Auckland, New Zealand | SMX Ltd | smx.co.nz http://smx.co.nz SMX | Business Email Specialists The information contained in this email and any attachments is confidential. If you are not the intended recipient then you must not use, disseminate, distribute or copy any information contained in this email or any attachments. If you have received this email in error or you are not the originally intended recipient please contact SMX immediately and destroy this email. -- 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] Index Scan taking long time
Bryce Ewing br...@smx.co.nz writes: So it seems to me that once the index is in memory everything is fine with the world, but the loading of the index into memory is horrendous. So it would seem. What's the disk hardware on this machine? It's possible that part of the problem is table bloat, leading to the indexscan having to fetch many more pages than it would if the table were more compact. 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
[PERFORM] Index Scan taking long time
Hi, I have been trying to fix a performance issue that we have which I have tracked down to index scans being done on a particular table (or set of tables): The following query: explain analyze select * FROM inbound.event_20090526 e LEFT OUTER JOIN inbound.internal_host i ON (e.mta_host_id = i.id) LEFT OUTER JOIN inbound.internal_host iaa ON (e.aamta_host_id = iaa.id) LEFT OUTER JOIN inbound.event_status es ON (e.event_status_id = es.id) LEFT OUTER JOIN inbound.threat t ON (e.threat_id = t.id), inbound.domain d, inbound.event_type et WHERE e.domain_id = d.id AND e.event_type_id = et.id AND d.name IN ( 'testdomain.com' ); Does this: QUERY PLAN -- Nested Loop Left Join (cost=0.00..10887.03 rows=8 width=2021) (actual time=50.352..14378.603 rows=3453 loops=1) - Nested Loop Left Join (cost=0.00..10882.23 rows=8 width=1985) (actual time=50.346..14372.820 rows=3453 loops=1) - Nested Loop Left Join (cost=0.00..10877.43 rows=8 width=1949) (actual time=50.336..14358.101 rows=3453 loops=1) - Nested Loop Left Join (cost=0.00..10872.63 rows=8 width=1801) (actual time=50.321..14344.603 rows=3453 loops=1) - Nested Loop (cost=0.00..10867.83 rows=8 width=1764) (actual time=50.315..14336.979 rows=3453 loops=1) - Nested Loop (cost=0.00..10863.03 rows=8 width=1728) (actual time=50.288..14308.368 rows=3453 loops=1) - Index Scan using domain_name_idx on domain d (cost=0.00..6.63 rows=1 width=452) (actual time=0.049..0.080 rows=1 loops=1) Index Cond: ((name)::text = 'testdomain.com'::text) - Index Scan using event_20090526_domain_idx on event_20090526 e (cost=0.00..10694.13 rows=3606 width=1276) (actual time=50.233..14305.211 rows=3453 loops=1) Index Cond: (e.domain_id = d.id) - Index Scan using event_type_pkey on event_type et (cost=0.00..0.56 rows=1 width=36) (actual time=0.006..0.006 rows=1 loops=3453) Index Cond: (et.id = e.event_type_id) - Index Scan using threat_pkey on threat t (cost=0.00..0.56 rows=1 width=37) (actual time=0.000..0.000 rows=0 loops=3453) Index Cond: (e.threat_id = t.id) - Index Scan using event_status_pkey on event_status es (cost=0.00..0.56 rows=1 width=148) (actual time=0.002..0.002 rows=1 loops=3453) Index Cond: (e.event_status_id = es.id) - Index Scan using internal_host_pkey on internal_host iaa (cost=0.00..0.56 rows=1 width=36) (actual time=0.002..0.003 rows=1 loops=3453) Index Cond: (e.aamta_host_id = iaa.id) - Index Scan using internal_host_pkey on internal_host i (cost=0.00..0.56 rows=1 width=36) (actual time=0.000..0.000 rows=0 loops=3453) Index Cond: (e.mta_host_id = i.id) Total runtime: 14380.000 ms If the same query is done straight away again we get: QUERY PLAN - Nested Loop Left Join (cost=0.00..10887.03 rows=8 width=2021) (actual time=0.165..67.388 rows=3453 loops=1) - Nested Loop Left Join (cost=0.00..10882.23 rows=8 width=1985) (actual time=0.162..61.973 rows=3453 loops=1) - Nested Loop Left Join (cost=0.00..10877.43 rows=8 width=1949) (actual time=0.156..49.756 rows=3453 loops=1) - Nested Loop Left Join (cost=0.00..10872.63 rows=8 width=1801) (actual time=0.148..37.522 rows=3453 loops=1) - Nested Loop (cost=0.00..10867.83 rows=8 width=1764) (actual time=0.146..31.920 rows=3453 loops=1) - Nested Loop (cost=0.00..10863.03 rows=8 width=1728) (actual time=0.129..10.325 rows=3453 loops=1) - Index Scan using domain_name_idx on domain d (cost=0.00..6.63 rows=1 width=452) (actual time=0.099..0.139 rows=1 loops=1) Index Cond: ((name)::text = 'rhe.com.au'::text) - Index Scan using event_20090526_domain_idx on event_20090526 e (cost=0.00..10694.13 rows=3606 width=1276) (actual time=0.027..7.510 rows=3453 loops=1)
Re: [PERFORM] Index Scan taking long time
On Tue, Jun 16, 2009 at 9:30 PM, Bryce Ewingbr...@smx.co.nz wrote: Hi, I have been trying to fix a performance issue that we have which I have tracked down to index scans being done on a particular table (or set of tables): The following query: explain analyze select * FROM inbound.event_20090526 e LEFT OUTER JOIN inbound.internal_host i ON (e.mta_host_id = i.id) LEFT OUTER JOIN inbound.internal_host iaa ON (e.aamta_host_id = iaa.id) LEFT OUTER JOIN inbound.event_status es ON (e.event_status_id = es.id) LEFT OUTER JOIN inbound.threat t ON (e.threat_id = t.id), inbound.domain d, inbound.event_type et WHERE e.domain_id = d.id AND e.event_type_id = et.id AND d.name IN ( 'testdomain.com' ); Without looking at the explain just yet, it seems to me that you are constraining the order of joins to insist that the left joins be done first, then the regular joins second, because of your mix of explicit and implicit join syntax. The query planner is constrained to run explicit joins first, then implicit if I remember correctly. So, making it all explicit might help. Might not. But it's a thought -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Index scan plan estimates way off.
I have a relatively simple query with a single index on (contract_id, time): vjtrade= EXPLAIN SELECT * FROM ticks WHERE contract_id=1 ORDER BY time; QUERY PLAN - Sort (cost=11684028.44..11761274.94 rows=30898601 width=40) Sort Key: time - Bitmap Heap Scan on ticks (cost=715657.57..6995196.08 rows=30898601 width=40) Recheck Cond: (contract_id = 1) - Bitmap Index Scan on contract_id_time_idx (cost=0.00..707932.92 rows=30898601 width=0) Index Cond: (contract_id = 1) (6 rows) This plan doesn't complete in a reasonable amount of time. I end up having to kill the query after it's been running for over an hour. If I do a: SET enable_sort=FALSE; SET enable_bitmapscan=FALSE; Then it gives me this plan: Index Scan using contract_id_time_idx on ticks (cost=0.00..117276552.51 rows=30897044 width=40) (actual time=34.025..738583.609 rows=27858174 loops=1) Index Cond: (contract_id = 1) Total runtime: 742323.102 ms Notice how the estimated cost is so much different from the actual time. The row estimate is pretty good, however. This is on postgresql 8.3.5 with: shared_buffers = 512MB temp_buffers = 256MB work_mem = 256MB max_fsm_pages = 153600 effective_cache_size = 1500MB Is there any way to give postgresql a better estimate of the index scan time? I tried setting random_page_cost=1, but it still gave me the bitmap plan. Thanks, Jonathan Hseu
Re: [PERFORM] Index scan plan estimates way off.
Jonathan Hseu vom...@vomjom.net writes: Sort (cost=11684028.44..11761274.94 rows=30898601 width=40) Sort Key: time - Bitmap Heap Scan on ticks (cost=715657.57..6995196.08 rows=30898601 width=40) Recheck Cond: (contract_id = 1) - Bitmap Index Scan on contract_id_time_idx (cost=0.00..707932.92 rows=30898601 width=0) Index Cond: (contract_id = 1) (6 rows) This plan doesn't complete in a reasonable amount of time. I end up having to kill the query after it's been running for over an hour. The bitmap scan should be at least as efficient as the plain indexscan, so I suppose the problem is that the sort is slow. What's the datatype of time? Can this machine actually support 256MB+ work_mem, or is that likely to be driving it into swapping? You might learn more from enabling trace_sort and watching the postmaster log entries it generates. On the whole I think the planner isn't making a stupid choice here: sorting a large number of rows usually *is* preferable to making an indexscan over them, unless the table is remarkably close to being in physical order for the index. So it would be worth trying to figure out what the problem with the sort is. 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] Index scan plan estimates way off.
Oops, forgot to CC my reply to the list. Sorry if this gets messed up. On Thu, Mar 5, 2009 at 12:30 PM, Tom Lane t...@sss.pgh.pa.us wrote: Jonathan Hseu vom...@vomjom.net writes: Sort (cost=11684028.44..11761274.94 rows=30898601 width=40) Sort Key: time - Bitmap Heap Scan on ticks (cost=715657.57..6995196.08 rows=30898601 width=40) Recheck Cond: (contract_id = 1) - Bitmap Index Scan on contract_id_time_idx (cost=0.00..707932.92 rows=30898601 width=0) Index Cond: (contract_id = 1) (6 rows) This plan doesn't complete in a reasonable amount of time. I end up having to kill the query after it's been running for over an hour. The bitmap scan should be at least as efficient as the plain indexscan, so I suppose the problem is that the sort is slow. What's the datatype of time? It's a timestamp with time zone and not null. Can this machine actually support 256MB+ work_mem, or is that likely to be driving it into swapping? Yeah, the machine has 4 GB of RAM and isn't even close to swapping at all. You might learn more from enabling trace_sort and watching the postmaster log entries it generates. I got this (I'm not sure how to interpret it, as there doesn't seem to be any documentation about it on the web): 2009-03-05 15:28:27 CST STATEMENT: select * from ticks where contract_id=1 order by time limit 280; 2009-03-05 15:28:30 CST LOG: begin tuple sort: nkeys = 1, workMem = 262144, randomAccess = f 2009-03-05 15:28:30 CST STATEMENT: explain analyze select * from ticks where contract_id=1 order by time limit 280; 2009-03-05 16:50:31 CST LOG: switching to external sort with 937 tapes: CPU 26.57s/4835.39u sec elapsed 4921.38 sec 2009-03-05 16:50:31 CST STATEMENT: explain analyze select * from ticks where contract_id=1 order by time limit 280; 2009-03-05 17:00:46 CST LOG: performsort starting: CPU 92.51s/4955.58u sec elapsed 5536.57 sec 2009-03-05 17:00:46 CST STATEMENT: explain analyze select * from ticks where contract_id=1 order by time limit 280; 2009-03-05 17:00:50 CST LOG: finished writing run 1 to tape 0: CPU 92.86s/4958.30u sec elapsed 5539.78 sec 2009-03-05 17:00:50 CST STATEMENT: explain analyze select * from ticks where contract_id=1 order by time limit 280; 2009-03-05 17:00:50 CST LOG: finished writing final run 2 to tape 1: CPU 92.88s/4958.40u sec elapsed 5539.90 sec 2009-03-05 17:00:50 CST STATEMENT: explain analyze select * from ticks where contract_id=1 order by time limit 280; 2009-03-05 17:00:51 CST LOG: performsort done (except 2-way final merge): CPU 92.96s/4958.55u sec elapsed 5541.10 sec 2009-03-05 17:00:51 CST STATEMENT: explain analyze select * from ticks where contract_id=1 order by time limit 280; 2009-03-05 17:00:58 CST LOG: external sort ended, 204674 disk blocks used: CPU 93.36s/4960.04u sec elapsed 5548.33 sec 2009-03-05 17:00:58 CST STATEMENT: explain analyze select * from ticks where contract_id=1 order by time limit 280; On the whole I think the planner isn't making a stupid choice here: sorting a large number of rows usually *is* preferable to making an indexscan over them, unless the table is remarkably close to being in physical order for the index. So it would be worth trying to figure out what the problem with the sort is. I don't really understand this. It seems to me that fetching and sorting 30 million rows wouldn't be preferable to just fetching them in the correct order in the first place, even if it's in a random order. I tried another query with a much smaller result set, and the index scan takes 76 seconds, but the bitmap scan sort takes 1.5 hours. That's quite a difference. I'm pretty sure the physical order of the index is very different from the physical order of the table. The elements of the table are inserted in strictly time order, if that's how it ends up being on disk, whereas the index, as far as I understand it, would be sorted by the first of the multiple columns, the contract_id, then the time. Here's both of the EXPLAIN ANALYZEs for the same query: = explain analyze select * from ticks where contract_id=1 order by time limit 280; QUERY PLAN Limit (cost=10487812.41..10494812.41 rows=280 width=40) (actual time=5541109.704..5545345.598 rows=280 loops=1) - Sort (cost=10487812.41..10565267.29 rows=30981949 width=40) (actual time=5541109.702..5544883.149 rows=280 loops=1) Sort Key: time Sort Method: external merge Disk: 1637392kB - Bitmap Heap Scan on ticks (cost=718724.01..7015201.37 rows=30981949 width=40) (actual time=4874084.105..5465131.997 rows=27917481 loops=1) Recheck Cond: (contract_id = 1) - Bitmap Index Scan on contract_id_time_idx
Re: [PERFORM] Index scan plan estimates way off.
On Thu, Mar 5, 2009 at 1:30 PM, Tom Lane t...@sss.pgh.pa.us wrote: Jonathan Hseu vom...@vomjom.net writes: Sort (cost=11684028.44..11761274.94 rows=30898601 width=40) Sort Key: time - Bitmap Heap Scan on ticks (cost=715657.57..6995196.08 rows=30898601 width=40) Recheck Cond: (contract_id = 1) - Bitmap Index Scan on contract_id_time_idx (cost=0.00..707932.92 rows=30898601 width=0) Index Cond: (contract_id = 1) (6 rows) This plan doesn't complete in a reasonable amount of time. I end up having to kill the query after it's been running for over an hour. The bitmap scan should be at least as efficient as the plain indexscan, so I suppose the problem is that the sort is slow. What's the datatype of time? Can this machine actually support 256MB+ work_mem, or is that likely to be driving it into swapping? You might learn more from enabling trace_sort and watching the postmaster log entries it generates. On the whole I think the planner isn't making a stupid choice here: sorting a large number of rows usually *is* preferable to making an indexscan over them, unless the table is remarkably close to being in physical order for the index. It seems like this is only likely to be true if most of the data needs to be read from a magnetic disk, so that many seeks are involved. That might not be the case here, since the machine has an awful lot of RAM. ...Robert -- 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] index scan cost
Tom Lane wrote: Jeff Frost [EMAIL PROTECTED] writes: I have two postgresql servers. One runs 8.3.1, the other 8.3.3. On the 8.3.1 machine, the index scans are being planned extremely low cost: Index Scan using ix_email_entity_thread on email_entity (cost=0.00..4.59 rows=1 width=1031) (actual time=0.095..0.120 rows=4 loops=1) Index Cond: (email_thread = 375629157) Index Scan using ix_email_entity_thread on email_entity (cost=0.00..2218.61 rows=1151 width=931) (actual time=0.094..0.111 rows=4 loops=1) Index Cond: (email_thread = 375629157) This isn't a cost problem, this is a stats problem. Why does the second server think 1151 rows will be returned? Try comparing the pg_stats entries for the email_thread column on both servers ... seems like they must be significantly different. Sorry it took me a while to close the loop on this. So, the server that had the less desirable plan had actually been analyzed more recently by autovacuum. When I went back to compare the stats on the faster server, autovacuum had analyzed it and the plan was now more similar. Adjusting the stats target up for that column helped on both servers though it never did get back as close as before. -- Jeff Frost, Owner [EMAIL PROTECTED] Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 916-647-6411 FAX: 916-405-4032
[PERFORM] index scan cost
I have two postgresql servers. One runs 8.3.1, the other 8.3.3. On the 8.3.1 machine, the index scans are being planned extremely low cost: explain ANALYZE select * from email_entity where email_thread = 375629157; QUERY PLAN Index Scan using ix_email_entity_thread on email_entity (cost=0.00..4.59 rows=1 width=1031) (actual time=0.095..0.120 rows=4 loops=1) Index Cond: (email_thread = 375629157) Total runtime: 0.207 ms (3 rows) But on the 8.3.3 machine, the index scans are being planned much higher cost: explain ANALYZE select * from email_entity where email_thread = 375629157; QUERY PLAN - Index Scan using ix_email_entity_thread on email_entity (cost=0.00..2218.61 rows=1151 width=931) (actual time=0.094..0.111 rows=4 loops=1) Index Cond: (email_thread = 375629157) Total runtime: 0.253 ms (3 rows) diffing the 'show all;' output reveals the following (left side is the low cost plan, right side is the high cost plan server): 57c57 effective_cache_size| 31800MB | Sets the planner's assumption about the size of the disk cache. --- effective_cache_size| 15300MB | Sets the planner's assumption about the size of the disk cache. 72c72 fsync | on| Forces synchronization of updates to disk. --- fsync | off | Forces synchronization of updates to disk. 110c110 log_line_prefix | | Controls information prefixed to each log line. --- log_line_prefix | user=%u,db=%d | Controls information prefixed to each log line. 128,129c128,129 max_fsm_pages | 200 | Sets the maximum number of disk pages for which free space is tracked. max_fsm_relations | 1000 | Sets the maximum number of tables and indexes for which free space is tracked. --- max_fsm_pages | 400 | Sets the maximum number of disk pages for which free space is tracked. max_fsm_relations | 5000 | Sets the maximum number of tables and indexes for which free space is tracked. 145,146c145,146 server_version | 8.3.1 | Shows the server version. server_version_num | 80301 | Shows the server version as an integer. --- server_version | 8.3.3 | Shows the server version. server_version_num | 80303 | Shows the server version as an integer. 149c149 shared_preload_libraries| | Lists shared libraries to preload into server. --- shared_preload_libraries| $libdir/plugins/plugin_debugger.so| Lists shared libraries to preload into server. Disabling the debugger had no effect on the slow server. I then thought perhaps this was a difference between 8.3.1 and 8.3.3, so I loaded the DB on a separate test machine and tried the query with both 8.3.1 and 8.3.3 on the same server: engage=# show server_version; server_version 8.3.1 (1 row) explain ANALYZE select * from email_entity where email_thread = 375629157; QUERY PLAN -- Index Scan using ix_email_entity_thread on email_entity (cost=0.00..1319.44 rows=1183 width=1046) (actual time=0.017..0.022 rows=4 loops=1) Index Cond: (email_thread = 375629157) Total runtime: 0.054 ms (3 rows) engage=# show server_version; server_version 8.3.3 (1 row) explain ANALYZE select * from email_entity where email_thread = 375629157; QUERY PLAN -- Index Scan using ix_email_entity_thread on email_entity (cost=0.00..1319.44 rows=1183 width=1046) (actual time=0.018..0.022 rows=4 loops=1) Index Cond: (email_thread = 375629157) Total runtime: 0.055 ms (3 rows) As you might guess, the
Re: [PERFORM] index scan cost
On Fri, 18 Jul 2008, Dennis Brakhane wrote: The fast server makes a much more accurate estimation of the number of rows to expect (4 rows are returning, 1 was estimated). The slow server estimates 1151 rows. Try running ANALYZE on the slow one You're quite right. I probably didn't mention that the slow one has been analyzed several times. In fact, every time adjusted the statistics target for that column I analyzed, thus the eventually better, but still inaccurate estimates toward the bottom of the post. -- Jeff Frost, Owner [EMAIL PROTECTED] Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 -- 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] index scan cost
The fast server makes a much more accurate estimation of the number of rows to expect (4 rows are returning, 1 was estimated). The slow server estimates 1151 rows. Try running ANALYZE on the slow one -- 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] index scan cost
Jeff Frost [EMAIL PROTECTED] writes: I have two postgresql servers. One runs 8.3.1, the other 8.3.3. On the 8.3.1 machine, the index scans are being planned extremely low cost: Index Scan using ix_email_entity_thread on email_entity (cost=0.00..4.59 rows=1 width=1031) (actual time=0.095..0.120 rows=4 loops=1) Index Cond: (email_thread = 375629157) Index Scan using ix_email_entity_thread on email_entity (cost=0.00..2218.61 rows=1151 width=931) (actual time=0.094..0.111 rows=4 loops=1) Index Cond: (email_thread = 375629157) This isn't a cost problem, this is a stats problem. Why does the second server think 1151 rows will be returned? Try comparing the pg_stats entries for the email_thread column on both servers ... seems like they must be significantly different. 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
[PERFORM] Index Scan Backward + check additional condition before heap access
Hi, (PostgreSQL 8.3) I'm trying to optimize one of the most often used queries in our system: (Full minimized pastable schema and data below.) create table feeds_users ( user_id int references users(id) not null, feed_id int references feeds(id) not null, unique(user_id, feed_id) ); create table items ( id serial primary key, feed_id int references feeds(id) not null, title text, pub_date timestamp ); create index items_feed_id_idx on items(feed_id); create index items_pub_date_idx on items(pub_date); create index items_pub_date_feed_id_idx on items(pub_date, feed_id); create index feeds_users_feed_id on feeds_users(feed_id); -- Query variant 1: EXPLAIN ANALYZE SELECT i.* FROM items i WHERE feed_id IN ( SELECT f.feed_id FROM feeds_users f WHERE f.user_id = ?) ORDER BY pub_date DESC LIMIT 20 OFFSET 100; -- Query variant 2: EXPLAIN ANALYZE SELECT i.* FROM items i JOIN feeds_users f ON i.feed_id = f.feed_id AND f.user_id = ? ORDER BY pub_date DESC LIMIT 20 OFFSET 100; The table items contains 70 rows, feeds_users 99000, feeds 1 and users 1000. The number of feeds for each user is distributed logarithmically, i.e. there are many users with none to little feeds and some users with many feeds. In reality, 99% of the rows in items are being inserted in pub_date order, and the correlation of user_id in feeds_users is not 1 (it is 1 with the test data). I need this query to work blisteringly fast for the common case, and at least not too slow for extreme cases. Extreme cases are: * no feeds for a user * very little feeds for a user, with the top 20 items spread over 10% of table items * normal number of feeds for a user, but big offset (1000 or 1 or 10). The maximum offset could be capped in the application if needed, but a solution without that would be preferred. The common case is that the needed rows are within the first (by pub_date desc) 1% of items. I ran some tests of both query variants on a Pentium M 1.6 GHz notebook with 1280 MB RAM, shared_buffers = 32MB, temp_buffers 8MB, work_mem 8MB. Three different user_ids were used for testing; the needed rows for each user are either 1) not existant, 2) spread over 18% of the table, 3) spread over 0.064% of the table. Also I tried a statistics target of 10 and 100 for the two columns in feeds_users. Two query variants were used, one with an inner join and one with IN. I got 4 different plans all in all. Results: no. stat user_id item rowsresult rows variant plan time target scanned w/o limitquery 1 10 370 0in 1 2 ms 2 join 2 15000 ms 3 49 46855 (18%) 630 in 1 2300 ms 4 join 2 2300 ms 5 109 448 (0.064%) 206780 in 1 6 ms 6 join 2 9 ms 7 100370 0in 3 0.2 ms 8 join 2 16500 ms 9 49 46855 (18%) 630 in 4 10 ms 10join 2 2300 ms 11 109 448 (0.064%) 206780 in 1 6 ms 12join 2 9 ms Plans below. Now the questions: Do the differences in characteristics of the test data and the real data somehow invalidate these numbers? I observe, that the query variant with IN is faster in all cases. What's the difference in them that leads to plans being chosen that differ so much performance-wise? Can I somehow trigger the items_pub_date_feed_id_idx to be used? ISTM that scanning by that index in pub_date desc order and using that same index to test for a needed feed_id would be faster than accessing the heap for each tuple. With a statistics target of 100, in queries no 3 and 9 a different, a very much faster plan was chosen. How is the statistics target to be determined such that the faster plan is chosen? Am I going to have to increase the statistics target as one or more table receive more rows? Thanks Markus Plans: 1 (for no 3) Limit (cost=1304.78..1565.74 rows=20 width=27) (actual time= 2121.866..2377.740 rows=20 loops=1) - Nested Loop IN Join (cost=0.00..57984.39 rows= width=27) (actual time=9.856..2377.421 rows=120 loops=1) - Index Scan Backward using items_pub_date_idx on items i (cost= 0.00..37484.20 rows=700071 width=27) (actual time=0.131..1152.933rows=127337 loops=1) - Index Scan using feeds_users_user_id_key on feeds_users f (cost=0.00..0.29 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=127337) Index Cond: ((f.user_id = 49) AND (f.feed_id = i.feed_id)) Total runtime: 2377.899 ms (6 rows) 2 (for no. 4) Limit (cost=542.78..651.33 rows=20 width=27) (actual time= 2133.759..2393.259
Re: [PERFORM] index scan through a subquery
Tom Lane wrote: I need the lovely index scan, but my table is hidden behind a view, and all I get is the ugly sequential scan. Any ideas on how to convince the optimizer to unfold the subquery properly? You should provide some context in this sort of gripe, like which PG version you're using. But I'm going to guess that it's 8.2.x, because 8.1.x gets it right :-(. Try the attached. Good guess; I was indeed talking about the current release rather than the previous release. Also, apologies for the tone of my post: I was attempting to be jovial, but in retrospect, I see how it reads as a gripe, which I guess evoked your frowny-face emoticon. Thanks for the quick response, elegant fix, and ongoing excellent work! Cheers, Bill Index: planagg.c === RCS file: /cvsroot/pgsql/src/backend/optimizer/plan/planagg.c,v retrieving revision 1.25 diff -c -r1.25 planagg.c *** planagg.c 9 Jan 2007 02:14:13 - 1.25 --- planagg.c 6 Feb 2007 06:30:23 - *** *** 70,75 --- 70,76 optimize_minmax_aggregates(PlannerInfo *root, List *tlist, Path *best_path) { Query *parse = root-parse; + FromExpr *jtnode; RangeTblRef *rtr; RangeTblEntry *rte; RelOptInfo *rel; *** *** 102,115 * We also restrict the query to reference exactly one table, since join * conditions can't be handled reasonably. (We could perhaps handle a * query containing cartesian-product joins, but it hardly seems worth the ! * trouble.) */ ! Assert(parse-jointree != NULL IsA(parse-jointree, FromExpr)); ! if (list_length(parse-jointree-fromlist) != 1) ! return NULL; ! rtr = (RangeTblRef *) linitial(parse-jointree-fromlist); ! if (!IsA(rtr, RangeTblRef)) return NULL; rte = rt_fetch(rtr-rtindex, parse-rtable); if (rte-rtekind != RTE_RELATION || rte-inh) return NULL; --- 103,121 * We also restrict the query to reference exactly one table, since join * conditions can't be handled reasonably. (We could perhaps handle a * query containing cartesian-product joins, but it hardly seems worth the ! * trouble.) However, the single real table could be buried in several ! * levels of FromExpr. */ ! jtnode = parse-jointree; ! while (IsA(jtnode, FromExpr)) ! { ! if (list_length(jtnode-fromlist) != 1) ! return NULL; ! jtnode = linitial(jtnode-fromlist); ! } ! if (!IsA(jtnode, RangeTblRef)) return NULL; + rtr = (RangeTblRef *) jtnode; rte = rt_fetch(rtr-rtindex, parse-rtable); if (rte-rtekind != RTE_RELATION || rte-inh) return NULL; ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PERFORM] index scan through a subquery
Why should these queries have different plans? create table foo (a int PRIMARY KEY); Q1: explain select max(a) from foo Result (cost=0.04..0.05 rows=1 width=0) InitPlan - Limit (cost=0.00..0.04 rows=1 width=4) - Index Scan Backward using foo_pkey on foo (cost=0.00..76.10 rows=2140 width=4) Filter: (a IS NOT NULL) Q2: explain select max(a) from (select * from foo) as f Aggregate (cost=36.75..36.76 rows=1 width=4) - Seq Scan on foo (cost=0.00..31.40 rows=2140 width=4) I need the lovely index scan, but my table is hidden behind a view, and all I get is the ugly sequential scan. Any ideas on how to convince the optimizer to unfold the subquery properly? Bill ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] index scan through a subquery
Bill Howe [EMAIL PROTECTED] writes: I need the lovely index scan, but my table is hidden behind a view, and all I get is the ugly sequential scan. Any ideas on how to convince the optimizer to unfold the subquery properly? You should provide some context in this sort of gripe, like which PG version you're using. But I'm going to guess that it's 8.2.x, because 8.1.x gets it right :-(. Try the attached. regards, tom lane Index: planagg.c === RCS file: /cvsroot/pgsql/src/backend/optimizer/plan/planagg.c,v retrieving revision 1.25 diff -c -r1.25 planagg.c *** planagg.c 9 Jan 2007 02:14:13 - 1.25 --- planagg.c 6 Feb 2007 06:30:23 - *** *** 70,75 --- 70,76 optimize_minmax_aggregates(PlannerInfo *root, List *tlist, Path *best_path) { Query *parse = root-parse; + FromExpr *jtnode; RangeTblRef *rtr; RangeTblEntry *rte; RelOptInfo *rel; *** *** 102,115 * We also restrict the query to reference exactly one table, since join * conditions can't be handled reasonably. (We could perhaps handle a * query containing cartesian-product joins, but it hardly seems worth the !* trouble.) */ ! Assert(parse-jointree != NULL IsA(parse-jointree, FromExpr)); ! if (list_length(parse-jointree-fromlist) != 1) ! return NULL; ! rtr = (RangeTblRef *) linitial(parse-jointree-fromlist); ! if (!IsA(rtr, RangeTblRef)) return NULL; rte = rt_fetch(rtr-rtindex, parse-rtable); if (rte-rtekind != RTE_RELATION || rte-inh) return NULL; --- 103,121 * We also restrict the query to reference exactly one table, since join * conditions can't be handled reasonably. (We could perhaps handle a * query containing cartesian-product joins, but it hardly seems worth the !* trouble.) However, the single real table could be buried in several !* levels of FromExpr. */ ! jtnode = parse-jointree; ! while (IsA(jtnode, FromExpr)) ! { ! if (list_length(jtnode-fromlist) != 1) ! return NULL; ! jtnode = linitial(jtnode-fromlist); ! } ! if (!IsA(jtnode, RangeTblRef)) return NULL; + rtr = (RangeTblRef *) jtnode; rte = rt_fetch(rtr-rtindex, parse-rtable); if (rte-rtekind != RTE_RELATION || rte-inh) return NULL; ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PERFORM] Index scan startup time
[Apologies if this already went through. I don't see it in the archives.] Normally one expects that an index scan would have a startup time of nearly zero. Can anyone explain this: EXPLAIN ANALYZE select activity_id from activity where state in (1, 10001) order by activity_id limit 100; QUERY PLAN Limit (cost=0.00..622.72 rows=100 width=8) (actual time=207356.054..207356.876 rows=100 loops=1) - Index Scan using activity_pk on activity (cost=0.00..40717259.91 rows=6538650 width=8) (actual time=207356.050..207356.722 rows=100 loops=1) Filter: ((state = 1) OR (state = 10001)) Total runtime: 207357.000 ms The table has seen VACUUM FULL and REINDEX before this. The plan choice and the statistics look right, but why does it take 3 minutes before doing anything? Or is the measurement of the actual start time inaccurate? This is quite reproducible, so it's not just a case of a temporary I/O bottleneck, say. (PostgreSQL 8.0.3) -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Index scan startup time
On Thu, Mar 30, 2006 at 01:59:10PM +0200, Peter Eisentraut wrote: EXPLAIN ANALYZE select activity_id from activity where state in (1, 10001) order by activity_id limit 100; QUERY PLAN Limit (cost=0.00..622.72 rows=100 width=8) (actual time=207356.054..207356.876 rows=100 loops=1) - Index Scan using activity_pk on activity (cost=0.00..40717259.91 rows=6538650 width=8) (actual time=207356.050..207356.722 rows=100 loops=1) Filter: ((state = 1) OR (state = 10001)) Total runtime: 207357.000 ms The table has seen VACUUM FULL and REINDEX before this. The index scan is by activity_id, not by state. Do you have an index on state at all? /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Index scan startup time
On Thu, Mar 30, 2006 at 01:59:10PM +0200, Peter Eisentraut wrote: The table has seen VACUUM FULL and REINDEX before this. But no analyze? Mike Stone ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Index scan startup time
Am Donnerstag, 30. März 2006 14:02 schrieb Steinar H. Gunderson: On Thu, Mar 30, 2006 at 01:59:10PM +0200, Peter Eisentraut wrote: EXPLAIN ANALYZE select activity_id from activity where state in (1, 10001) order by activity_id limit 100; QUERY PLAN Limit (cost=0.00..622.72 rows=100 width=8) (actual time=207356.054..207356.876 rows=100 loops=1) - Index Scan using activity_pk on activity (cost=0.00..40717259.91 rows=6538650 width=8) (actual time=207356.050..207356.722 rows=100 loops=1) Filter: ((state = 1) OR (state = 10001)) Total runtime: 207357.000 ms The table has seen VACUUM FULL and REINDEX before this. The index scan is by activity_id, not by state. Do you have an index on state at all? There is an index on state as well but the column is not selective enough. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Index scan startup time
Am Donnerstag, 30. März 2006 14:06 schrieb Michael Stone: On Thu, Mar 30, 2006 at 01:59:10PM +0200, Peter Eisentraut wrote: The table has seen VACUUM FULL and REINDEX before this. But no analyze? ANALYZE as well, but the plan choice is not the point anyway. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Index scan startup time
On Thu, Mar 30, 2006 at 02:23:53PM +0200, Peter Eisentraut wrote: EXPLAIN ANALYZE select activity_id from activity where state in (1, 10001) order by activity_id limit 100; QUERY PLAN Limit (cost=0.00..622.72 rows=100 width=8) (actual time=207356.054..207356.876 rows=100 loops=1) - Index Scan using activity_pk on activity (cost=0.00..40717259.91 rows=6538650 width=8) (actual time=207356.050..207356.722 rows=100 loops=1) Filter: ((state = 1) OR (state = 10001)) Total runtime: 207357.000 ms The table has seen VACUUM FULL and REINDEX before this. The index scan is by activity_id, not by state. Do you have an index on state at all? There is an index on state as well but the column is not selective enough. Well, it's logical enough; it scans along activity_id until it finds one with state=1 or state=10001. You obviously have a _lot_ of records with low activity_id and state none of these two, so Postgres needs to scan all those records before it founds 100 it can output. This is the “startup cost” you're seeing. /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Index scan startup time
Hi, Peter, Peter Eisentraut wrote: The table has seen VACUUM FULL and REINDEX before this. But no analyze? ANALYZE as well, but the plan choice is not the point anyway. Maybe you could add a combined Index on activity_id and state, or (if you use this kind of query more often) a conditional index on activity_id where state in (1,10001). Btw, PostgreSQL 8.1 could AND two bitmap index scans on the activity and state indices, and get the result faster (i presume). Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Index scan startup time
On Thu, Mar 30, 2006 at 02:31:34PM +0200, Steinar H. Gunderson wrote: Well, it's logical enough; it scans along activity_id until it finds one with state=1 or state=10001. You obviously have a _lot_ of records with low activity_id and state none of these two, so Postgres needs to scan all those records before it founds 100 it can output. This is the “startup cost” you're seeing. Yes. And the estimates are bad enough (orders of magnitude) that I can't help but wonder whether pg could come up with a better plan with better statistics: - Index Scan using activity_pk on activity (cost=0.00..40717259.91 rows=6538650 width=8) (actual time=207356.050..207356.722 rows=100 loops=1) Filter: ((state = 1) OR (state = 10001)) Mike Stone ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Index scan startup time
Am Donnerstag, 30. März 2006 14:31 schrieb Steinar H. Gunderson: Well, it's logical enough; it scans along activity_id until it finds one with state=1 or state=10001. You obviously have a _lot_ of records with low activity_id and state none of these two, so Postgres needs to scan all those records before it founds 100 it can output. This is the “startup cost” you're seeing. The startup cost is the cost until the plan is set up to start outputting rows. It is not the time until the first row is found. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Index scan startup time
On Thu, Mar 30, 2006 at 02:59:02PM +0200, Peter Eisentraut wrote: Well, it's logical enough; it scans along activity_id until it finds one with state=1 or state=10001. You obviously have a _lot_ of records with low activity_id and state none of these two, so Postgres needs to scan all those records before it founds 100 it can output. This is the “startup cost” you're seeing. The startup cost is the cost until the plan is set up to start outputting rows. It is not the time until the first row is found. Well, point, my terminology was wrong. Still, what you're seeing is endless scanning for the first row. I don't know your distribution, but are you really sure state wouldn't have better selectivity? /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Index scan startup time
On Thu, Mar 30, 2006 at 02:51:47PM +0200, Steinar H. Gunderson wrote: On Thu, Mar 30, 2006 at 07:42:53AM -0500, Michael Stone wrote: Yes. And the estimates are bad enough (orders of magnitude) that I can't help but wonder whether pg could come up with a better plan with better statistics: - Index Scan using activity_pk on activity (cost=0.00..40717259.91 rows=6538650 width=8) (actual time=207356.050..207356.722 rows=100 loops=1) Filter: ((state = 1) OR (state = 10001)) Isn't the rows=100 here because of the LIMIT? Yes. I was looking at the other side; I thought pg could estimate how much work it would have to do to hit the limit, but double-checking it looks like it can't. Mike Stone ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Index scan startup time
Michael Stone [EMAIL PROTECTED] writes: Yes. I was looking at the other side; I thought pg could estimate how much work it would have to do to hit the limit, but double-checking it looks like it can't. Yes, it does, you just have to understand how to interpret the EXPLAIN output. Peter had Limit (cost=0.00..622.72 rows=100 width=8) (actual time=207356.054..207356.876 rows=100 loops=1) - Index Scan using activity_pk on activity (cost=0.00..40717259.91 rows=6538650 width=8) (actual time=207356.050..207356.722 rows=100 loops=1) Filter: ((state = 1) OR (state = 10001)) Total runtime: 207357.000 ms Notice that the total cost of the LIMIT node is estimated as far less than the total cost of the IndexScan node. That's exactly because the planner doesn't expect the indexscan to run to completion. The problem here appears to be a non-random correlation between state and activity, such that the desired state values are not randomly scattered in the activity sequence. The planner doesn't know about that correlation and hence can't predict the poor startup time. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Index scan startup time
Tom Lane wrote: The problem here appears to be a non-random correlation between state and activity, such that the desired state values are not randomly scattered in the activity sequence. The planner doesn't know about that correlation and hence can't predict the poor startup time. So from when to when is the startup time (the x in x..y) actually measured? When does the clock start ticking and when does it stop? That is what's confusing me. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Index scan startup time
Peter Eisentraut [EMAIL PROTECTED] writes: So from when to when is the startup time (the x in x..y) actually measured? When does the clock start ticking and when does it stop? That is what's confusing me. The planner thinks of the startup time (the first estimated-cost number) as the time before the output scan can start, eg, time to do the sort in a sort node. EXPLAIN ANALYZE however reports the actual time until the first output row is delivered. When you've got a filter applied to the node result, as in this case, there can be a material difference between the two definitions, because of the time spent scanning rows that don't get past the filter. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] Index Scan Costs versus Sort
This is related to my post the other day about sort performance. Part of my problem seems to be that postgresql is greatly overestimating the cost of index scans. As a result, it prefers query plans that involve seq scans and sorts versus query plans that use index scans. Here is an example query: SELECT tlid, count(tlid) FROM completechain GROUP BY tlid; Approach #1 - seq scan with sort: GroupAggregate (cost=10177594.61..11141577.89 rows=48199164 width=4) (actual time=7439085.877..8429628.234 rows=47599910 loops=1) - Sort (cost=10177594.61..10298092.52 rows=48199164 width=4) (actual time=7439085.835..8082452.721 rows=48199165 loops=1) Sort Key: tlid - Seq Scan on completechain (cost=0.00..2229858.64 rows=48199164 width=4) (actual time=10.788..768403.874 rows=48199165 loops=1) Total runtime: 8596987.505 ms Approach #2 - index scan (done by setting enable_seqscan to false and enable_sort to false): GroupAggregate (cost=0.00..113713861.43 rows=48199164 width=4) (actual time=53.211..2652227.201 rows=47599910 loops=1) - Index Scan using idx_completechain_tlid on completechain (cost=0.00..112870376.06 rows=48199164 width=4) (actual time=53.168..2312426.321 rows=48199165 loops=1) Total runtime: 2795420.933 ms Approach #1 is estimated to be 10 times less costly, yet takes 3 times longer to execute. My questions: 1. Postgresql estimates the index scan will be 50 times more costly than the seq scan (112870376 vs 2229858) yet in fact it only takes 3 times longer to execute (2312426 s vs. 768403 s). My understanding is that postgresql assumes, via the random_page_cost parameter, that an index scan will take 4 times longer than a sequential scan. So why is the analyzer estimating it is 50 times slower? 2. In approach #1, the planner thinks the sort will take roughly 4 times longer [(10,298,092 - 2,229,858) / 2,229,858] than the sequential scan. Yet it really takes almost ten times longer. It seems as is the planner is greatly underestimating the sort cost? Due to these two apparent miscalculations, postgresql is choosing the wrong query plan to execute this query. I've attached my postgresql.conf file below just in case this is due to some misconfiguration on my part. Some setup notes: * All tables are vacuumed and analyzed * Running Postgresql 8.1 on Suse 10 * Low end hardware - Dell Dimension 3000, 1GB ram, 1 built-in 80 GB IDE drive, 1 SATA Seagate 400GB drive. The IDE drive has the OS and the WAL files, the SATA drive the database. From hdparm the max IO for the IDE drive is about 50Mb/s and the SATA drive is about 65Mb/s. --- #--- # RESOURCE USAGE (except WAL) #--- shared_buffers = 4 # 4 buffers * 8192 bytes/buffer = 327,680,000 bytes #shared_buffers = 1000# min 16 or max_connections*2, 8KB each temp_buffers = 5000 #temp_buffers = 1000# min 100, 8KB each #max_prepared_transactions = 5# can be 0 or more # note: increasing max_prepared_transactions costs ~600 bytes of shared memory # per transaction slot, plus lock space (see max_locks_per_transaction). work_mem = 16384# in Kb #work_mem = 1024# min 64, size in KB maintenance_work_mem = 262144# in kb #maintenance_work_mem = 16384# min 1024, size in KB #max_stack_depth = 2048# min 100, size in KB # - Free Space Map - max_fsm_pages = 6 #max_fsm_pages = 2# min max_fsm_relations*16, 6 bytes each #max_fsm_relations = 1000# min 100, ~70 bytes each # - Kernel Resource Usage - #max_files_per_process = 1000# min 25 #preload_libraries = '' # - Cost-Based Vacuum Delay - #vacuum_cost_delay = 0# 0-1000 milliseconds #vacuum_cost_page_hit = 1# 0-1 credits #vacuum_cost_page_miss = 10# 0-1 credits #vacuum_cost_page_dirty = 20# 0-1 credits #vacuum_cost_limit = 200# 0-1 credits # - Background writer - #bgwriter_delay = 200# 10-1 milliseconds between rounds #bgwriter_lru_percent = 1.0# 0-100% of LRU buffers scanned/round #bgwriter_lru_maxpages = 5# 0-1000 buffers max written/round #bgwriter_all_percent = 0.333# 0-100% of all buffers scanned/round #bgwriter_all_maxpages = 5# 0-1000 buffers max written/round #--- # WRITE AHEAD LOG #--- # - Settings - fsync = on# turns forced synchronization on or off #wal_sync_method = fsync# the default is the first option # supported by the operating system: # open_datasync #
Re: [PERFORM] Index Scan Costs versus Sort
Charlie Savage [EMAIL PROTECTED] writes: 1. Postgresql estimates the index scan will be 50 times more costly than the seq scan (112870376 vs 2229858) yet in fact it only takes 3 times longer to execute (2312426 s vs. 768403 s). My understanding is that postgresql assumes, via the random_page_cost parameter, that an index scan will take 4 times longer than a sequential scan. So why is the analyzer estimating it is 50 times slower? The other factors that are likely to affect this are index correlation and effective cache size. It's fairly remarkable that a full-table index scan only takes 3 times longer than a seqscan; you must have both a high correlation and a reasonably large cache. You showed us your effective_cache_size setting, but what's the pg_stats entry for completechain.tlid contain? Can you quantify what the physical ordering of tlid values is likely to be? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Index Scan Costs versus Sort
Hi Tom, From pg_stats: schema = tiger; tablename = completechain; attname = tlid; null_frac = 0; avg_width = 4; n_distinct = -1; most_common_vals = ; most_common_freqs = ; correlation = 0.155914; Note that I have default_statistics_target set to 100. Here is the first few values from histogram_bounds: {102450,2202250,4571797,6365754,8444936,10541593,12485818,14545727,16745594,18421868,20300549,22498643,24114709,26301001,28280632,30370123,32253657,33943046,35898115,37499478,39469054,41868498,43992143,45907830,47826340,49843926,52051798,54409298,56447416, The tlid column is a US Census bureau ID assigned to each chain in the US - where a chain is a road segment, river segment, railroad segment, etc. The data is loaded on state-by-state basis, and then a county-by-county basis. There is no overall ordering to TLIDs, although perhaps there is some local ordering at the county level (but from a quick look at the data I don't see any, and the correlation factor indicates there isn't any if I am interpreting it correctly). Any other info that would be helpful to see? Charlie Tom Lane wrote: Charlie Savage [EMAIL PROTECTED] writes: 1. Postgresql estimates the index scan will be 50 times more costly than the seq scan (112870376 vs 2229858) yet in fact it only takes 3 times longer to execute (2312426 s vs. 768403 s). My understanding is that postgresql assumes, via the random_page_cost parameter, that an index scan will take 4 times longer than a sequential scan. So why is the analyzer estimating it is 50 times slower? The other factors that are likely to affect this are index correlation and effective cache size. It's fairly remarkable that a full-table index scan only takes 3 times longer than a seqscan; you must have both a high correlation and a reasonably large cache. You showed us your effective_cache_size setting, but what's the pg_stats entry for completechain.tlid contain? Can you quantify what the physical ordering of tlid values is likely to be? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Index Scan Costs versus Sort
Following up with some additional information. The machine has 1Gb physical RAM. When I run the query (with sort and seqscan enabled), top reports (numbers are fairly consistent): Mem: 1,032,972k total, 1,019,516k used, 13,412k free, 17,132k buffers Swap: 2,032,140k total, 17,592k used, 2,014,548k free, 742,636k cached The postmaster process is using 34.7% of RAM - 359m virt, 349 res, 319m. No other process is using more than 2% of the memory. From vmstat: r b swpd free buffcache 1 0 1759213568 17056743676 vmstat also shows no swapping going on. Note that I have part of the database, for just Colorado, on my Windows XP laptop (table size for completechain table in this case is 1Gb versus 18Gb for the whole US) for development purposes. I see the same behavior on it, which is a Dell D6100 laptop with 1Gb, running 8.1, and a default postgres.conf file with three changes (shared_buffers set to 7000, and work_mem set to 8192, effective_cache_size 2500). Out of curiosity, how much longer would an index_scan expected to be versus a seq scan? I was under the impression it would be about a facto of 4, or is that not usually the case? Thanks for the help, Charlie ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] index scan on =, but not ?
On Thu, 10 Mar 2005 10:24:46 +1000, David Brown [EMAIL PROTECTED] wrote: What concerns me is that this all depends on the correlation factor, and I suspect that the planner is not giving enough weight to this. The planner does the right thing for correlations very close to 1 (and -1) and for correlations near zero. For correlations somewhere between 0 and 1 the cost is estimated by interpolation, but it tends too much towards the bad end, IMHO. Servus Manfred ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] index scan on =, but not ?
Assuming your system isn't starved for memory, shouldn't repeated page fetches be hitting the cache? I've also wondered about the conventional wisdom that read ahead doesn't help random reads. I may well be missing something, but *if* the OS has enough memory to cache most of the table, surely read ahead will still work to your advantage? Bruno Wolff III wrote: No. When you are doing an index scan of a significant part of the table, you will fetch some heap pages more than once. You will also be fetching blocks out of order, so you will lose out on read ahead optimization by the OS. This assumes that you don't get a lot of cache hits on the help pages. If a significant portion of the table is cached, then the trade off point will be at a higher percentage of the table. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] index scan on =, but not ?
Jim C. Nasby wrote: Ahh, I was thinking of a high correlation factor on the index. I still question 5% though... that seems awefully low. Not really. It all depends on how many records you're packing into each page. 1% may well be the threshold for small records. Tom mentioned this in the last couple of months. He was citing a uniform distribution as an example and I thought that sounded a little pessimistic, but when I did the (possibly faulty) math with a random distribution, I discovered he wasn't far off. It's not this simple, but if you can fit 50 randomly organized records into each page and you want to retrieve 2% of the rows, it's likely you'll have to fetch every page - believe it or not. What concerns me is that this all depends on the correlation factor, and I suspect that the planner is not giving enough weight to this. Actually, I'm wondering if it's even looking at the statistic, but I haven't created a test to check. It might explain quite a few complaints about the planner not utilizing indexes. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] index scan of whole table, can't see why
On Wed, 2005-01-19 at 21:00 -0800, [EMAIL PROTECTED] wrote: Let's see if I have been paying enough attention to the SQL gurus. The planner is making a different estimate of how many deprecated'' versus how many broken ''. I would try SET STATISTICS to a larger number on the ports table, and re-analyze. that should not help, as the estimate is accurate, according to the explain analyze. gnari ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] index scan of whole table, can't see why
On Wed, 2005-01-19 at 20:37 -0500, Dan Langille wrote: Hi folks, Running on 7.4.2, recently vacuum analysed the three tables in question. The query plan in question changes dramatically when a WHERE clause changes from ports.broken to ports.deprecated. I don't see why. Well, I do see why: a sequential scan of a 130,000 rows. The query goes from 13ms to 1100ms because the of this. The full plans are at http://rafb.net/paste/results/v8ccvQ54.html I have tried some tuning by: set effective_cache_size to 4000, was 1000 set random_page_cost to 1, was 4 The resulting plan changes, but no speed improvment, are at http://rafb.net/paste/results/rV8khJ18.html this just confirms that an indexscan is not always better than a tablescan. by setting random_page_cost to 1, you deceiving the planner into thinking that the indexscan is almost as effective as a tablescan. Any suggestions please? did you try to increase sort_mem ? gnari ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] index scan of whole table, can't see why
On 20 Jan 2005 at 9:34, Ragnar Hafstað wrote: On Wed, 2005-01-19 at 20:37 -0500, Dan Langille wrote: Hi folks, Running on 7.4.2, recently vacuum analysed the three tables in question. The query plan in question changes dramatically when a WHERE clause changes from ports.broken to ports.deprecated. I don't see why. Well, I do see why: a sequential scan of a 130,000 rows. The query goes from 13ms to 1100ms because the of this. The full plans are at http://rafb.net/paste/results/v8ccvQ54.html I have tried some tuning by: set effective_cache_size to 4000, was 1000 set random_page_cost to 1, was 4 The resulting plan changes, but no speed improvment, are at http://rafb.net/paste/results/rV8khJ18.html this just confirms that an indexscan is not always better than a tablescan. by setting random_page_cost to 1, you deceiving the planner into thinking that the indexscan is almost as effective as a tablescan. Any suggestions please? did you try to increase sort_mem ? I tried sort_mem = 4096 and then 16384. This did not make a difference. See http://rafb.net/paste/results/AVDqEm55.html Thank you. -- Dan Langille : http://www.langille.org/ BSDCan - The Technical BSD Conference - http://www.bsdcan.org/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] index scan of whole table, can't see why
On Wed, 19 Jan 2005, Dan Langille wrote: Hi folks, Running on 7.4.2, recently vacuum analysed the three tables in question. The query plan in question changes dramatically when a WHERE clause changes from ports.broken to ports.deprecated. I don't see why. Well, I do see why: a sequential scan of a 130,000 rows. The query goes from 13ms to 1100ms because the of this. The full plans are at http://rafb.net/paste/results/v8ccvQ54.html I have tried some tuning by: set effective_cache_size to 4000, was 1000 set random_page_cost to 1, was 4 The resulting plan changes, but no speed improvment, are at http://rafb.net/paste/results/rV8khJ18.html Any suggestions please? As a question, what does it do if enable_hashjoin is false? I'm wondering if it'll pick a nested loop for that step for the element/ports join and what it estimates the cost to be. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] index scan of whole table, can't see why
On 20 Jan 2005 at 6:14, Stephan Szabo wrote: On Wed, 19 Jan 2005, Dan Langille wrote: Hi folks, Running on 7.4.2, recently vacuum analysed the three tables in question. The query plan in question changes dramatically when a WHERE clause changes from ports.broken to ports.deprecated. I don't see why. Well, I do see why: a sequential scan of a 130,000 rows. The query goes from 13ms to 1100ms because the of this. The full plans are at http://rafb.net/paste/results/v8ccvQ54.html I have tried some tuning by: set effective_cache_size to 4000, was 1000 set random_page_cost to 1, was 4 The resulting plan changes, but no speed improvment, are at http://rafb.net/paste/results/rV8khJ18.html Any suggestions please? As a question, what does it do if enable_hashjoin is false? I'm wondering if it'll pick a nested loop for that step for the element/ports join and what it estimates the cost to be. With enable_hashjoin = false, no speed improvement. Execution plan at http://rafb.net/paste/results/qtSFVM72.html thanks -- Dan Langille : http://www.langille.org/ BSDCan - The Technical BSD Conference - http://www.bsdcan.org/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] index scan of whole table, can't see why
On Thu, 20 Jan 2005, Dan Langille wrote: On 20 Jan 2005 at 6:14, Stephan Szabo wrote: On Wed, 19 Jan 2005, Dan Langille wrote: Hi folks, Running on 7.4.2, recently vacuum analysed the three tables in question. The query plan in question changes dramatically when a WHERE clause changes from ports.broken to ports.deprecated. I don't see why. Well, I do see why: a sequential scan of a 130,000 rows. The query goes from 13ms to 1100ms because the of this. The full plans are at http://rafb.net/paste/results/v8ccvQ54.html I have tried some tuning by: set effective_cache_size to 4000, was 1000 set random_page_cost to 1, was 4 The resulting plan changes, but no speed improvment, are at http://rafb.net/paste/results/rV8khJ18.html Any suggestions please? As a question, what does it do if enable_hashjoin is false? I'm wondering if it'll pick a nested loop for that step for the element/ports join and what it estimates the cost to be. With enable_hashjoin = false, no speed improvement. Execution plan at http://rafb.net/paste/results/qtSFVM72.html Honestly I expected it to be slower (which it was), but I figured it's worth seeing what alternate plans it'll generate (specifically to see how it cost a nested loop on that join to compare to the fast plan). Unfortunately, it generated a merge join, so I think it might require both enable_hashjoin=false and enable_mergejoin=false to get it which is likely to be even slower in practice but still may be useful to see. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] index scan of whole table, can't see why
On 20 Jan 2005 at 7:26, Stephan Szabo wrote: On Thu, 20 Jan 2005, Dan Langille wrote: On 20 Jan 2005 at 6:14, Stephan Szabo wrote: On Wed, 19 Jan 2005, Dan Langille wrote: Hi folks, Running on 7.4.2, recently vacuum analysed the three tables in question. The query plan in question changes dramatically when a WHERE clause changes from ports.broken to ports.deprecated. I don't see why. Well, I do see why: a sequential scan of a 130,000 rows. The query goes from 13ms to 1100ms because the of this. The full plans are at http://rafb.net/paste/results/v8ccvQ54.html I have tried some tuning by: set effective_cache_size to 4000, was 1000 set random_page_cost to 1, was 4 The resulting plan changes, but no speed improvment, are at http://rafb.net/paste/results/rV8khJ18.html Any suggestions please? As a question, what does it do if enable_hashjoin is false? I'm wondering if it'll pick a nested loop for that step for the element/ports join and what it estimates the cost to be. With enable_hashjoin = false, no speed improvement. Execution plan at http://rafb.net/paste/results/qtSFVM72.html Honestly I expected it to be slower (which it was), but I figured it's worth seeing what alternate plans it'll generate (specifically to see how it cost a nested loop on that join to compare to the fast plan). Unfortunately, it generated a merge join, so I think it might require both enable_hashjoin=false and enable_mergejoin=false to get it which is likely to be even slower in practice but still may be useful to see. Setting both to false gives a dramatic performance boost. See http://rafb.net/paste/results/b70KAi42.html This gives suitable speed, but why does the plan vary so much with such a minor change in the WHERE clause? -- Dan Langille : http://www.langille.org/ BSDCan - The Technical BSD Conference - http://www.bsdcan.org/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] index scan of whole table, can't see why
On Fri, 21 Jan 2005 02:36 am, Dan Langille wrote: On 20 Jan 2005 at 7:26, Stephan Szabo wrote: [snip] Honestly I expected it to be slower (which it was), but I figured it's worth seeing what alternate plans it'll generate (specifically to see how it cost a nested loop on that join to compare to the fast plan). Unfortunately, it generated a merge join, so I think it might require both enable_hashjoin=false and enable_mergejoin=false to get it which is likely to be even slower in practice but still may be useful to see. Setting both to false gives a dramatic performance boost. See http://rafb.net/paste/results/b70KAi42.html - Materialize (cost=15288.70..15316.36 rows=2766 width=35) (actual time=0.004..0.596 rows=135 loops=92) - Nested Loop (cost=0.00..15288.70 rows=2766 width=35) (actual time=0.060..9.130 rows=135 loops=1) The Planner here has a quite inaccurate guess at the number of rows that will match in the join. An alternative to turning off join types is to up the statistics on the Element columns because that's where the join is happening. Hopefully the planner will get a better idea. However it may not be able too. 2766 rows vs 135 is quite likely to choose different plans. As you can see you have had to turn off two join types to give something you wanted/expected. This gives suitable speed, but why does the plan vary so much with such a minor change in the WHERE clause? Plan 1 - broken - Nested Loop (cost=0.00..3825.30 rows=495 width=35) (actual time=0.056..16.161 rows=218 loops=1) Plan 2 - deprecated - Hash Join (cost=3676.78..10144.06 rows=2767 width=35) (actual time=7.638..1158.128 rows=135 loops=1) The performance difference is when the where is changed, you have a totally different set of selection options. The Plan 1 and Plan 2 shown from your paste earlier, report that you are out by a factor of 2 for plan 1. But for plan 2 its a factor of 20. The planner is likely to make the wrong choice when the stats are out by that factor. Beware what is a small typing change does not mean they queries are anything alight. Regards Russell Smith. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] index scan of whole table, can't see why
On 21 Jan 2005 at 8:38, Russell Smith wrote: On Fri, 21 Jan 2005 02:36 am, Dan Langille wrote: On 20 Jan 2005 at 7:26, Stephan Szabo wrote: [snip] Honestly I expected it to be slower (which it was), but I figured it's worth seeing what alternate plans it'll generate (specifically to see how it cost a nested loop on that join to compare to the fast plan). Unfortunately, it generated a merge join, so I think it might require both enable_hashjoin=false and enable_mergejoin=false to get it which is likely to be even slower in practice but still may be useful to see. Setting both to false gives a dramatic performance boost. See http://rafb.net/paste/results/b70KAi42.html - Materialize (cost=15288.70..15316.36 rows=2766 width=35) (actual time=0.004..0.596 rows=135 loops=92) - Nested Loop (cost=0.00..15288.70 rows=2766 width=35) (actual time=0.060..9.130 rows=135 loops=1) The Planner here has a quite inaccurate guess at the number of rows that will match in the join. An alternative to turning off join types is to up the statistics on the Element columns because that's where the join is happening. Hopefully the planner will get a better idea. However it may not be able too. 2766 rows vs 135 is quite likely to choose different plans. As you can see you have had to turn off two join types to give something you wanted/expected. Fair comment. However, the statistics on ports.element_id, ports.deprecated, ports.broken, and element.id are both set to 1000. This gives suitable speed, but why does the plan vary so much with such a minor change in the WHERE clause? Plan 1 - broken - Nested Loop (cost=0.00..3825.30 rows=495 width=35) (actual time=0.056..16.161 rows=218 loops=1) Plan 2 - deprecated - Hash Join (cost=3676.78..10144.06 rows=2767 width=35) (actual time=7.638..1158.128 rows=135 loops=1) The performance difference is when the where is changed, you have a totally different set of selection options. The Plan 1 and Plan 2 shown from your paste earlier, report that you are out by a factor of 2 for plan 1. But for plan 2 its a factor of 20. The planner is likely to make the wrong choice when the stats are out by that factor. Beware what is a small typing change does not mean they queries are anything alight. Agreed. I just did not expect such a dramatic change which a result set that is similar. Actually, they aren't that similar at all. Thank you. -- Dan Langille : http://www.langille.org/ BSDCan - The Technical BSD Conference - http://www.bsdcan.org/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[PERFORM] index scan of whole table, can't see why
Hi folks, Running on 7.4.2, recently vacuum analysed the three tables in question. The query plan in question changes dramatically when a WHERE clause changes from ports.broken to ports.deprecated. I don't see why. Well, I do see why: a sequential scan of a 130,000 rows. The query goes from 13ms to 1100ms because the of this. The full plans are at http://rafb.net/paste/results/v8ccvQ54.html I have tried some tuning by: set effective_cache_size to 4000, was 1000 set random_page_cost to 1, was 4 The resulting plan changes, but no speed improvment, are at http://rafb.net/paste/results/rV8khJ18.html Any suggestions please? -- Dan Langille : http://www.langille.org/ BSDCan - The Technical BSD Conference - http://www.bsdcan.org/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] index scan of whole table, can't see why
Let's see if I have been paying enough attention to the SQL gurus. The planner is making a different estimate of how many deprecated'' versus how many broken ''. I would try SET STATISTICS to a larger number on the ports table, and re-analyze. ---(end of broadcast)--- TIP 8: explain analyze is your friend