Re: [PERFORM] Index scan cost calculation

> From: Jim Nasby <jim.na...@bluetreble.com> >To: Jeff Janes <jeff.ja...@gmail.com>; Glyn Astill <glynast...@yahoo.co.uk> >Cc: Pgsql-performance <pgsql-performance@postgresql.org> >Sent: Wednesday, 2 December 2015, 22:32 >Subject: Re: [PERFORM] Index scan

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

Re: [PERFORM] Index scan cost calculation

> >Clauses that can't be used in an "indexable" way are excluded from the >index selectivity, but not from the total query selectivity. > >> Or is it just likely that the selection of the new index is just by chance? > >Bingo. > Got it, thanks! Very much appreciated. Glyn -- Sent via

Re: [PERFORM] Index scan cost calculation

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

Re: [PERFORM] Index scan cost calculation

> From: Jeff Janes <jeff.ja...@gmail.com> > To: Glyn Astill <glynast...@yahoo.co.uk> > Cc: Pgsql-performance <pgsql-performance@postgresql.org> > Sent: Saturday, 28 November 2015, 19:25 > Subject: Re: [PERFORM] Index scan cost calculation > > &

Re: [PERFORM] Index scan cost calculation

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

[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

Re: [PERFORM] Index scan cost calculation

- Original Message - > From: Glyn Astill <glynast...@yahoo.co.uk> > To: Pgsql-performance <pgsql-performance@postgresql.org> > Sent: Thursday, 26 November 2015, 16:11 > Subject: [PERFORM] Index scan cost calculation > > Hi All, > > Using pg 9.4.5 I'

Re: [PERFORM] Index scan cost calculation

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

Re: [PERFORM] Index scan cost calculation

- Original Message - > From: Tom Lane <t...@sss.pgh.pa.us> > To: Glyn Astill <glynast...@yahoo.co.uk> > Cc: Pgsql-performance <pgsql-performance@postgresql.org> > Sent: Thursday, 26 November 2015, 16:44 > Subject: Re: [PERFORM] Index scan cost calc

Re: [PERFORM] Index scan cost calculation

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

Re: [PERFORM] Index Scan Backward Slow

On 01.05.2015 13:06, David Osborne wrote: Simple... that did it... thanks! dev= create index on table(code,row_id); CREATE INDEX Time: 38088.482 ms dev= explain (analyse,buffers) select row_id as last_row_id from table where code='XX' order by row_id desc limit 1; Just out of curiosity: Is

[PERFORM] Index Scan Backward Slow

Hi, We have a query which finds the latest row_id for a particular code. We've found a backwards index scan is much slower than a forward one, to the extent that disabling indexscan altogether actually improves the query time. Can anyone suggest why this might be, and what's best to do to

Re: [PERFORM] Index Scan Backward Slow

Simple... that did it... thanks! dev= create index on table(code,row_id); CREATE INDEX Time: 38088.482 ms dev= explain (analyse,buffers) select row_id as last_row_id from table where code='XX' order by row_id desc limit 1; QUERY PLAN

Re: [PERFORM] Index Scan Backward Slow

On 01 May 2015, at 13:54, David Osborne da...@qcode.co.uk wrote: Hi, We have a query which finds the latest row_id for a particular code. We've found a backwards index scan is much slower than a forward one, to the extent that disabling indexscan altogether actually improves the query

Re: [PERFORM] index scan forward vs backward = speed difference of 357X slower!

Kevin Traster ktras...@freshgrillfoods.com writes: The query plan and estimates are exactly the same, except desc has index scan backwards instead of index scan for changes_shareschange. Yet, actual runtime performance is different by 357x slower for the ascending version instead of

Re: [PERFORM] index scan forward vs backward = speed difference of 357X slower!

Typo: Work_mem = 32 MB The definition for both column and index: shareschange | numeric | changes_shareschange btree (shareschange) Index created using: CREATE INDEX changes_shareschange ON changes(shareschange); The entire table is created nightly (and analyzed afterwords),

Re: [PERFORM] index scan forward vs backward = speed difference of 357X slower!

On Wed, Feb 8, 2012 at 1:58 PM, Kevin Traster ktras...@freshgrillfoods.com wrote: Typo: Work_mem = 32 MB The definition for both column and index:  shareschange                  | numeric | changes_shareschange btree (shareschange) Index created using: CREATE INDEX changes_shareschange ON

Re: [PERFORM] index scan forward vs backward = speed difference of 357X slower!

This is not a problem with dead rows, but the index is not really satisfying your query and the database has to look through an indeterminate amount of rows until the 'limit 15' is satisfied.  Yeah, backwards scans are slower, especially for disk bound scans but you also have to consider how

Re: [PERFORM] index scan forward vs backward = speed difference of 357X slower!

Kevin Traster ktras...@freshgrillfoods.com wrote: I have indexes also on activity and mfiled (both btree) - wouldn't the database use them? - Kevin It will use them if they are part of the plan which had the lowest cost when it compared the costs of all possible plans. You haven't really

[PERFORM] index scan forward vs backward = speed difference of 357X slower!

PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-51), 64-bit Dedicated DB server 4GB ram Shared_Buffers = 1 GB Effective_cache_size = 3GB Work_mem = 32GB Analyze done Queries ran multiple times, same differences/results Default Statistics =

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

please provide non-default config options on this host plus the same from a host which is using an index scan, please. Also, postgresql version, OS, and all of the other stuff that is asked for in this document: http://wiki.postgresql.org/wiki/SlowQueryQuestions. It is impossible to say why the

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

On Thu, Oct 21, 2010 at 1:51 AM, Samuel Gendler sgend...@ideasculptor.com wrote: please provide non-default config options on this host plus the same from a host which is using an index scan, please.  Also, postgresql version, OS, and all of the other stuff that is asked for in this document: 

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

AI Rumman wrote: I don't know why seq scan is running on the following query where the same query is giving index scan on other servers: explain analyze select * from act where act.acttype in ( 'Meeting','Call','Task'); QUERY PLAN

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

-Original Message- From: AI Rumman [mailto:rumman...@gmail.com] Sent: Thursday, October 21, 2010 1:25 AM To: pgsql-performance@postgresql.org Subject: Index scan is not working, why?? I don't know why seq scan is running on the following query where the same query is giving

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

I don't know why seq scan is running on the following query where the same query is giving index scan on other servers: explain analyze select * from act where act.acttype in ( 'Meeting','Call','Task'); QUERY PLAN

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

15.10.2010 22:29, Tom Lane: Nikolai Zhubrn-a-zh...@yandex.ru writes: So, in (1, 2) condition is not in Index Cond anymore! Why is that? How can I push it back? It thinks the indexscan condition is sufficiently selective already. An = ANY condition like that will force multiple index

[PERFORM] Index scan / Index cond limitation or ?

Hello people, I'm having trouble to persuade index scan to check all of the conditions I specify _inside_ index cond. That is, _some_ condition always get pushed out of index cond and applied later (which will often result, for my real table contents, in too many unwanted rows initially hit

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

Nikolai Zhubr n-a-zh...@yandex.ru writes: So, in (1, 2) condition is not in Index Cond anymore! Why is that? How can I push it back? It thinks the indexscan condition is sufficiently selective already. An = ANY condition like that will force multiple index searches, one for each of the OR'd

Re: [PERFORM] Index Scan taking long time

Hi Tom, We have managed to improve significantly on the speed of this query. The way that we did this was through clustering the table based on the domain index which significantly reduced the page reads that were required in order to perform the query. Also to find this we turned on

Re: [PERFORM] Index Scan taking long time

Scott Marlowe scott.marl...@gmail.com writes: Without looking at the explain just yet, it seems to me that you are constraining the order of joins to insist that the left joins be done first, then the regular joins second, because of your mix of explicit and implicit join syntax. The query

Re: [PERFORM] Index Scan taking long time

The nested loops (which are due to the joins) don't seem to be part of the problem at all. The main time that is taken (actual time that is) is in this part: Index Scan using event_20090526_domain_idx on event_20090526 e (cost=0.00..10694.13 rows=3606 width=1276) (actual

Re: [PERFORM] Index Scan taking long time

Bryce Ewing br...@smx.co.nz writes: So it seems to me that once the index is in memory everything is fine with the world, but the loading of the index into memory is horrendous. So it would seem. What's the disk hardware on this machine? It's possible that part of the problem is table bloat,

[PERFORM] Index Scan taking long time

Hi, I have been trying to fix a performance issue that we have which I have tracked down to index scans being done on a particular table (or set of tables): The following query: explain analyze select * FROM inbound.event_20090526 e LEFT OUTER JOIN inbound.internal_host i ON (e.mta_host_id =

Re: [PERFORM] Index Scan taking long time

On Tue, Jun 16, 2009 at 9:30 PM, Bryce Ewingbr...@smx.co.nz wrote: Hi, I have been trying to fix a performance issue that we have which I have tracked down to index scans being done on a particular table (or set of tables): The following query: explain analyze select * FROM

[PERFORM] Index scan plan estimates way off.

I have a relatively simple query with a single index on (contract_id, time): vjtrade= EXPLAIN SELECT * FROM ticks WHERE contract_id=1 ORDER BY time; QUERY PLAN

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

Jonathan Hseu vom...@vomjom.net writes: Sort (cost=11684028.44..11761274.94 rows=30898601 width=40) Sort Key: time - Bitmap Heap Scan on ticks (cost=715657.57..6995196.08 rows=30898601 width=40) Recheck Cond: (contract_id = 1) - Bitmap Index Scan on

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

Oops, forgot to CC my reply to the list. Sorry if this gets messed up. On Thu, Mar 5, 2009 at 12:30 PM, Tom Lane t...@sss.pgh.pa.us wrote: Jonathan Hseu vom...@vomjom.net writes: Sort (cost=11684028.44..11761274.94 rows=30898601 width=40) Sort Key: time - Bitmap Heap Scan on

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

On Thu, Mar 5, 2009 at 1:30 PM, Tom Lane t...@sss.pgh.pa.us wrote: Jonathan Hseu vom...@vomjom.net writes:  Sort  (cost=11684028.44..11761274.94 rows=30898601 width=40)    Sort Key: time    -  Bitmap Heap Scan on ticks  (cost=715657.57..6995196.08 rows=30898601 width=40)          Recheck

Re: [PERFORM] index scan cost

Tom Lane wrote: Jeff Frost [EMAIL PROTECTED] writes: I have two postgresql servers. One runs 8.3.1, the other 8.3.3. On the 8.3.1 machine, the index scans are being planned extremely low cost: Index Scan using ix_email_entity_thread on email_entity (cost=0.00..4.59 rows=1

[PERFORM] index scan cost

I have two postgresql servers. One runs 8.3.1, the other 8.3.3. On the 8.3.1 machine, the index scans are being planned extremely low cost: explain ANALYZE select * from email_entity where email_thread = 375629157; QUERY PLAN

Re: [PERFORM] index scan cost

On Fri, 18 Jul 2008, Dennis Brakhane wrote: The fast server makes a much more accurate estimation of the number of rows to expect (4 rows are returning, 1 was estimated). The slow server estimates 1151 rows. Try running ANALYZE on the slow one You're quite right. I probably didn't mention

Re: [PERFORM] index scan cost

The fast server makes a much more accurate estimation of the number of rows to expect (4 rows are returning, 1 was estimated). The slow server estimates 1151 rows. Try running ANALYZE on the slow one -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to

Re: [PERFORM] index scan cost

Jeff Frost [EMAIL PROTECTED] writes: I have two postgresql servers. One runs 8.3.1, the other 8.3.3. On the 8.3.1 machine, the index scans are being planned extremely low cost: Index Scan using ix_email_entity_thread on email_entity (cost=0.00..4.59 rows=1 width=1031) (actual

[PERFORM] Index Scan Backward + check additional condition before heap access

Hi, (PostgreSQL 8.3) I'm trying to optimize one of the most often used queries in our system: (Full minimized pastable schema and data below.) create table feeds_users ( user_id int references users(id) not null, feed_id int references feeds(id) not null, unique(user_id, feed_id)

Re: [PERFORM] index scan through a subquery

Tom Lane wrote: I need the lovely index scan, but my table is hidden behind a view, and all I get is the ugly sequential scan. Any ideas on how to convince the optimizer to unfold the subquery properly? You should provide some context in this sort of gripe, like which PG version you're

[PERFORM] index scan through a subquery

Why should these queries have different plans? create table foo (a int PRIMARY KEY); Q1: explain select max(a) from foo Result (cost=0.04..0.05 rows=1 width=0) InitPlan - Limit (cost=0.00..0.04 rows=1 width=4) - Index Scan Backward using foo_pkey on foo

Re: [PERFORM] index scan through a subquery

Bill Howe [EMAIL PROTECTED] writes: I need the lovely index scan, but my table is hidden behind a view, and all I get is the ugly sequential scan. Any ideas on how to convince the optimizer to unfold the subquery properly? You should provide some context in this sort of gripe, like which PG

[PERFORM] Index scan startup time

[Apologies if this already went through. I don't see it in the archives.] Normally one expects that an index scan would have a startup time of nearly zero. Can anyone explain this: EXPLAIN ANALYZE select activity_id from activity where state in (1, 10001) order by activity_id limit 100;

Re: [PERFORM] Index scan startup time

On Thu, Mar 30, 2006 at 01:59:10PM +0200, Peter Eisentraut wrote: EXPLAIN ANALYZE select activity_id from activity where state in (1, 10001) order by activity_id limit 100; QUERY PLAN Limit (cost=0.00..622.72 rows=100 width=8) (actual time=207356.054..207356.876 rows=100 loops=1)

Re: [PERFORM] Index scan startup time

On Thu, Mar 30, 2006 at 01:59:10PM +0200, Peter Eisentraut wrote: The table has seen VACUUM FULL and REINDEX before this. But no analyze? Mike Stone ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ?

Re: [PERFORM] Index scan startup time

Am Donnerstag, 30. März 2006 14:02 schrieb Steinar H. Gunderson: On Thu, Mar 30, 2006 at 01:59:10PM +0200, Peter Eisentraut wrote: EXPLAIN ANALYZE select activity_id from activity where state in (1, 10001) order by activity_id limit 100; QUERY PLAN Limit (cost=0.00..622.72

Re: [PERFORM] Index scan startup time

Am Donnerstag, 30. März 2006 14:06 schrieb Michael Stone: On Thu, Mar 30, 2006 at 01:59:10PM +0200, Peter Eisentraut wrote: The table has seen VACUUM FULL and REINDEX before this. But no analyze? ANALYZE as well, but the plan choice is not the point anyway. -- Peter Eisentraut

Re: [PERFORM] Index scan startup time

On Thu, Mar 30, 2006 at 02:23:53PM +0200, Peter Eisentraut wrote: EXPLAIN ANALYZE select activity_id from activity where state in (1, 10001) order by activity_id limit 100; QUERY PLAN Limit (cost=0.00..622.72 rows=100 width=8) (actual time=207356.054..207356.876 rows=100 loops=1) -

Re: [PERFORM] Index scan startup time

Hi, Peter, Peter Eisentraut wrote: The table has seen VACUUM FULL and REINDEX before this. But no analyze? ANALYZE as well, but the plan choice is not the point anyway. Maybe you could add a combined Index on activity_id and state, or (if you use this kind of query more often) a conditional

Re: [PERFORM] Index scan startup time

On Thu, Mar 30, 2006 at 02:31:34PM +0200, Steinar H. Gunderson wrote: Well, it's logical enough; it scans along activity_id until it finds one with state=1 or state=10001. You obviously have a _lot_ of records with low activity_id and state none of these two, so Postgres needs to scan all

Re: [PERFORM] Index scan startup time

Am Donnerstag, 30. März 2006 14:31 schrieb Steinar H. Gunderson: Well, it's logical enough; it scans along activity_id until it finds one with state=1 or state=10001. You obviously have a _lot_ of records with low activity_id and state none of these two, so Postgres needs to scan all those

Re: [PERFORM] Index scan startup time

On Thu, Mar 30, 2006 at 02:59:02PM +0200, Peter Eisentraut wrote: Well, it's logical enough; it scans along activity_id until it finds one with state=1 or state=10001. You obviously have a _lot_ of records with low activity_id and state none of these two, so Postgres needs to scan all

Re: [PERFORM] Index scan startup time

On Thu, Mar 30, 2006 at 02:51:47PM +0200, Steinar H. Gunderson wrote: On Thu, Mar 30, 2006 at 07:42:53AM -0500, Michael Stone wrote: Yes. And the estimates are bad enough (orders of magnitude) that I can't help but wonder whether pg could come up with a better plan with better statistics: -

Re: [PERFORM] Index scan startup time

Michael Stone [EMAIL PROTECTED] writes: Yes. I was looking at the other side; I thought pg could estimate how much work it would have to do to hit the limit, but double-checking it looks like it can't. Yes, it does, you just have to understand how to interpret the EXPLAIN output. Peter had

Re: [PERFORM] Index scan startup time

Tom Lane wrote: The problem here appears to be a non-random correlation between state and activity, such that the desired state values are not randomly scattered in the activity sequence. The planner doesn't know about that correlation and hence can't predict the poor startup time. So from

Re: [PERFORM] Index scan startup time

Peter Eisentraut [EMAIL PROTECTED] writes: So from when to when is the startup time (the x in x..y) actually measured? When does the clock start ticking and when does it stop? That is what's confusing me. The planner thinks of the startup time (the first estimated-cost number) as the time

[PERFORM] Index Scan Costs versus Sort

This is related to my post the other day about sort performance. Part of my problem seems to be that postgresql is greatly overestimating the cost of index scans. As a result, it prefers query plans that involve seq scans and sorts versus query plans that use index scans. Here is an example

Re: [PERFORM] Index Scan Costs versus Sort

Charlie Savage [EMAIL PROTECTED] writes: 1. Postgresql estimates the index scan will be 50 times more costly than the seq scan (112870376 vs 2229858) yet in fact it only takes 3 times longer to execute (2312426 s vs. 768403 s). My understanding is that postgresql assumes, via the

Re: [PERFORM] Index Scan Costs versus Sort

Hi Tom, From pg_stats: schema = tiger; tablename = completechain; attname = tlid; null_frac = 0; avg_width = 4; n_distinct = -1; most_common_vals = ; most_common_freqs = ; correlation = 0.155914; Note that I have default_statistics_target set to 100. Here is the first few values from

Re: [PERFORM] Index Scan Costs versus Sort

Following up with some additional information. The machine has 1Gb physical RAM. When I run the query (with sort and seqscan enabled), top reports (numbers are fairly consistent): Mem: 1,032,972k total, 1,019,516k used, 13,412k free, 17,132k buffers Swap: 2,032,140k total, 17,592k used,

Re: [PERFORM] index scan on =, but not ?

On Thu, 10 Mar 2005 10:24:46 +1000, David Brown [EMAIL PROTECTED] wrote: What concerns me is that this all depends on the correlation factor, and I suspect that the planner is not giving enough weight to this. The planner does the right thing for correlations very close to 1 (and -1) and for

Re: [PERFORM] index scan on =, but not ?

Assuming your system isn't starved for memory, shouldn't repeated page fetches be hitting the cache? I've also wondered about the conventional wisdom that read ahead doesn't help random reads. I may well be missing something, but *if* the OS has enough memory to cache most of the table, surely

Re: [PERFORM] index scan on =, but not ?

Jim C. Nasby wrote: Ahh, I was thinking of a high correlation factor on the index. I still question 5% though... that seems awefully low. Not really. It all depends on how many records you're packing into each page. 1% may well be the threshold for small records. Tom mentioned this in the

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

On Wed, 2005-01-19 at 21:00 -0800, [EMAIL PROTECTED] wrote: Let's see if I have been paying enough attention to the SQL gurus. The planner is making a different estimate of how many deprecated'' versus how many broken ''. I would try SET STATISTICS to a larger number on the ports table,

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

On Wed, 2005-01-19 at 20:37 -0500, Dan Langille wrote: Hi folks, Running on 7.4.2, recently vacuum analysed the three tables in question. The query plan in question changes dramatically when a WHERE clause changes from ports.broken to ports.deprecated. I don't see why. Well, I do

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

On 20 Jan 2005 at 9:34, Ragnar Hafstað wrote: On Wed, 2005-01-19 at 20:37 -0500, Dan Langille wrote: Hi folks, Running on 7.4.2, recently vacuum analysed the three tables in question. The query plan in question changes dramatically when a WHERE clause changes from ports.broken to

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

On Wed, 19 Jan 2005, Dan Langille wrote: Hi folks, Running on 7.4.2, recently vacuum analysed the three tables in question. The query plan in question changes dramatically when a WHERE clause changes from ports.broken to ports.deprecated. I don't see why. Well, I do see why: a sequential

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

On 20 Jan 2005 at 6:14, Stephan Szabo wrote: On Wed, 19 Jan 2005, Dan Langille wrote: Hi folks, Running on 7.4.2, recently vacuum analysed the three tables in question. The query plan in question changes dramatically when a WHERE clause changes from ports.broken to

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

On Thu, 20 Jan 2005, Dan Langille wrote: On 20 Jan 2005 at 6:14, Stephan Szabo wrote: On Wed, 19 Jan 2005, Dan Langille wrote: Hi folks, Running on 7.4.2, recently vacuum analysed the three tables in question. The query plan in question changes dramatically when a WHERE

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

On 20 Jan 2005 at 7:26, Stephan Szabo wrote: On Thu, 20 Jan 2005, Dan Langille wrote: On 20 Jan 2005 at 6:14, Stephan Szabo wrote: On Wed, 19 Jan 2005, Dan Langille wrote: Hi folks, Running on 7.4.2, recently vacuum analysed the three tables in question. The

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

On Fri, 21 Jan 2005 02:36 am, Dan Langille wrote: On 20 Jan 2005 at 7:26, Stephan Szabo wrote: [snip] Honestly I expected it to be slower (which it was), but I figured it's worth seeing what alternate plans it'll generate (specifically to see how it cost a nested loop on that join to

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

On 21 Jan 2005 at 8:38, Russell Smith wrote: On Fri, 21 Jan 2005 02:36 am, Dan Langille wrote: On 20 Jan 2005 at 7:26, Stephan Szabo wrote: [snip] Honestly I expected it to be slower (which it was), but I figured it's worth seeing what alternate plans it'll generate (specifically

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

Hi folks, Running on 7.4.2, recently vacuum analysed the three tables in question. The query plan in question changes dramatically when a WHERE clause changes from ports.broken to ports.deprecated. I don't see why. Well, I do see why: a sequential scan of a 130,000 rows. The query goes

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

Let's see if I have been paying enough attention to the SQL gurus. The planner is making a different estimate of how many deprecated'' versus how many broken ''. I would try SET STATISTICS to a larger number on the ports table, and re-analyze. ---(end of