Re: [PERFORM] Index scan cost calculation

[Glyn Astill]

> 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

[Jim Nasby]

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

[Glyn Astill]

>
>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

[Jeff Janes]

On Mon, Nov 30, 2015 at 6:03 AM, Glyn Astill  wrote:
>
>
>
>
> 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

[Glyn Astill]

> 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

[Jeff Janes]

On Thu, Nov 26, 2015 at 8:11 AM, Glyn Astill  wrote:
> 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

Re: [PERFORM] Index scan cost calculation

[Glyn Astill]

- Original Message -

> From: Glyn Astill 
> To: Pgsql-performance 
> 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 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:
> 

Re: [PERFORM] Index scan cost calculation

[Tom Lane]

Glyn Astill  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.

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

[Glyn Astill]

- 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

[Tom Lane]

Glyn Astill  writes:
>> 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

[Robert Klemme]

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

Re: [PERFORM] Index Scan Backward Slow

[David Osborne]

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

[Evgeniy Shishkin]

 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!

[Tom Lane]

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!

[Kevin Traster]

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!

[Merlin Moncure]

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!

[Kevin Traster]

 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 Grittner]

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

Re: [PERFORM] Index scan is not working, why??

[Samuel Gendler]

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??

[Scott Marlowe]

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??

[Mladen Gogala]

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??

[Igor Neyman]

 

 -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

Re: [PERFORM] Index scan / Index cond limitation or ?

[Nikolai Zhubr]

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

Re: [PERFORM] Index scan / Index cond limitation or ?

[Tom Lane]

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

[Bryce Ewing]

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

[Tom Lane]

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

[Bryce Ewing]

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

[Tom Lane]

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]

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

Re: [PERFORM] Index scan plan estimates way off.

[Tom Lane]

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.

[Jonathan Hseu]

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.

[Robert Haas]

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

[Jeff Frost]

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

Re: [PERFORM] index scan cost

[Jeff Frost]

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

[Dennis Brakhane]

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

[Tom Lane]

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

Re: [PERFORM] index scan through a subquery

[Bill Howe]

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

Re: [PERFORM] index scan through a subquery

[Tom Lane]

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

Re: [PERFORM] Index scan startup time

[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?

/* 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

[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?

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

[Peter Eisentraut]

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

[Peter Eisentraut]

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

[Steinar H. Gunderson]

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

[Markus Schaber]

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

[Michael Stone]

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

[Peter Eisentraut]

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

[Steinar H. Gunderson]

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

[Michael Stone]

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

[Tom Lane]

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

[Peter Eisentraut]

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

[Tom Lane]

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

Re: [PERFORM] Index Scan Costs versus Sort

[Tom Lane]

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

[Charlie Savage]

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

[Charlie Savage]

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 ?

[Manfred Koizar]

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 ?

[David Brown]

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 ?

[David Brown]

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

[Ragnar Hafstað]

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

[Ragnar Hafstað]

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

[Dan Langille]

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

[Stephan Szabo]

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

[Dan Langille]

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

[Stephan Szabo]

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

[Dan Langille]

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

[Russell Smith]

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

[Dan Langille]

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]

Re: [PERFORM] index scan of whole table, can't see why

[andrew]

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