Index Scans become Seq Scans after VACUUM ANALYSE

Louis-David Mitterrand <vindex@apartia.org> writes:

While trying to optimise a query I found that running VACUUM ANALYSE
changed all the Index Scans to Seq Scans and that the only way to revert
to Index Scans was the add "enable_seqscan = 0" in postgresql.conf.

EXPLAIN ANALYZE output would be more interesting than just EXPLAIN.
Also, what does the pg_stats view show for these tables?

regards, tom lane

On Tue, Apr 16, 2002 at 10:41:57AM -0400, Tom Lane wrote:

Louis-David Mitterrand <vindex@apartia.org> writes:

While trying to optimise a query I found that running VACUUM ANALYSE
changed all the Index Scans to Seq Scans and that the only way to revert
to Index Scans was the add "enable_seqscan = 0" in postgresql.conf.

EXPLAIN ANALYZE output would be more interesting than just EXPLAIN.
Also, what does the pg_stats view show for these tables?

Thanks, pg_stats output is rather big so I attached it in a separate
file. Here are the EXPLAIN ANALYZE ouputs:

*********************************
* 1) With "enable_seqscan = 0": *
*********************************

gesci5=# explain analyse select p.id_prospect, p.position_prospect, initcap(p1.nom) as nom, initcap(p1.prenom) as prenom, a1.no_tel, a1.no_portable, p.dernier_contact, cn.id_contact, cn.id_vendeur, cn.id_operation, case when p.dernier_contact is not null then cn.date_contact::abstime::int4 else p.cree_le::abstime::int4 end as date_contact, cn.type_contact, cn.nouveau_rdv::abstime::int4 as nouveau_rdv, cn.date_echeance::abstime::int4 as date_echeance, cn.date_reponse::abstime::int4 as date_reponse from prospect p left join personne p1 on (p1.id_personne = p.id_personne1) left join adresse a1 on (a1.id_adresse = p1.id_adresse_principale) left join contact cn on (p.dernier_contact = cn.id_contact) where (p.abandon is null or p.abandon != 'O') order by cn.date_contact desc;
NOTICE: QUERY PLAN:

Sort (cost=49442.99..49442.99 rows=24719 width=123) (actual time=7281.98..7319.91 rows=23038 loops=1)
-> Hash Join (cost=14146.79..46656.05 rows=24719 width=123) (actual time=2619.85..6143.47 rows=23038 loops=1)
-> Merge Join (cost=9761.33..40724.83 rows=24719 width=66) (actual time=2061.31..3362.49 rows=23038 loops=1)
-> Sort (cost=9761.33..9761.33 rows=24719 width=49) (actual time=1912.73..1961.61 rows=23038 loops=1)
-> Merge Join (cost=0.00..7485.53 rows=24719 width=49) (actual time=42.98..1264.63 rows=23038 loops=1)
-> Index Scan using prospect_personne1 on prospect p (cost=0.00..4322.18 rows=24719 width=22) (actual time=0.28..528.42 rows=23038 loops=1)
-> Index Scan using personne_pkey on personne p1 (cost=0.00..2681.90 rows=44271 width=27) (actual time=0.18..384.11 rows=44302 loops=1)
-> Index Scan using adresse_pkey on adresse a1 (cost=0.00..30354.16 rows=95425 width=17) (actual time=0.44..738.99 rows=95456 loops=1)
-> Hash (cost=3242.09..3242.09 rows=30224 width=57) (actual time=557.04..557.04 rows=0 loops=1)
-> Index Scan using contact_pkey on contact cn (cost=0.00..3242.09 rows=30224 width=57) (actual time=0.26..457.97 rows=30224 loops=1)
Total runtime: 7965.74 msec

EXPLAIN

*********************************
* 2) With "enable_seqscan = 1": *
*********************************

NOTICE: QUERY PLAN:

Sort (cost=18622.67..18622.67 rows=24719 width=123) (actual time=10329.09..10367.06 rows=23039 loops=1)
-> Hash Join (cost=10034.30..15835.73 rows=24719 width=123) (actual time=1644.04..9397.53 rows=23039 loops=1)
-> Hash Join (cost=8074.99..12330.65 rows=24719 width=66) (actual time=1110.05..6475.65 rows=23039 loops=1)
-> Hash Join (cost=2088.54..4629.65 rows=24719 width=49) (actual time=385.33..2763.91 rows=23039 loops=1)
-> Seq Scan on prospect p (cost=0.00..1289.35 rows=24719 width=22) (actual time=0.34..361.31 rows=23039 loops=1)
-> Hash (cost=1106.71..1106.71 rows=44271 width=27) (actual time=381.91..381.91 rows=0 loops=1)
-> Seq Scan on personne p1 (cost=0.00..1106.71 rows=44271 width=27) (actual time=0.15..246.32 rows=44272 loops=1)
-> Hash (cost=2561.25..2561.25 rows=95425 width=17) (actual time=723.15..723.15 rows=0 loops=1)
-> Seq Scan on adresse a1 (cost=0.00..2561.25 rows=95425 width=17) (actual time=0.17..452.55 rows=95427 loops=1)
-> Hash (cost=1036.24..1036.24 rows=30224 width=57) (actual time=532.87..532.87 rows=0 loops=1)
-> Seq Scan on contact cn (cost=0.00..1036.24 rows=30224 width=57) (actual time=2.54..302.49 rows=30225 loops=1)
Total runtime: 10901.85 msec

EXPLAIN

--
HIPPOLYTE: Mais quels soins d�sormais peuvent me retarder ?
Assez dans les for�ts mon oisive jeunesse
Sur de vils ennemis a montr� son adresse.
(Ph�dre, J-B Racine, acte 3, sc�ne 5)

Reading all of this discussion lately about how the planner seems to
prefer seqscan's in alot of places where indexes would be better starts
making me wonder if some of the assumptions or cals made to figure costs
are wrong...

Anyone have any ideas?

Louis-David Mitterrand wrote:

I know I know, replying to myself is bad and probably means I'm going
insane but thought of one other thing...

Realistically the system should choos *ANY* index over a sequential
table scan. Above a fairly low number of records any indexed query
should be much faster than a seqscan. Am I right, or did I miss
something? (wouldn't be the first time I missed something)... Right
now the planner seems to think that index queries are more expensive
with a larger width than doing a seqscan on (possibly) more rows with a
narrower width.

Michael Loftis wrote:

On Tue, 16 Apr 2002, Michael Loftis wrote:

I know I know, replying to myself is bad and probably means I'm going
insane but thought of one other thing...

Realistically the system should choos *ANY* index over a sequential
table scan. Above a fairly low number of records any indexed query
should be much faster than a seqscan. Am I right, or did I miss
something? (wouldn't be the first time I missed something)... Right

Because the validity information is stored with the row and not the index
you have to read rows for any potential hit in the index. Depending on
the clustering of the table, the width of the rows and the percentage of
the table being hit by the scan (or estimated to be hit) you may read
most or all of the table as well as the index and be paying a penalty for
doing it randomly as opposed to be sequentially. IIRC, there are some
settings in the configuration that let you play around with the relative
costs the estimator uses (the random page cost and cpu costs for dealing
with index entries and such).

Michael Loftis <mloftis@wgops.com> writes:

Reading all of this discussion lately about how the planner seems to
prefer seqscan's in alot of places where indexes would be better starts
making me wonder if some of the assumptions or cals made to figure costs
are wrong...

Could well be. The sources are open, feel free to take a look ...
src/backend/optimizer/path/costsize.c is the epicenter ...

regards, tom lane

Michael Loftis <mloftis@wgops.com> writes:

Realistically the system should choos *ANY* index over a sequential
table scan.

Sorry, I do not accept that. You might as well say that we should
rip out any attempt at cost estimation, and instead put in two or
three lines of brain-dead heuristics. If it were that simple we'd
all be using MySQL ;-)

Above a fairly low number of records any indexed query
should be much faster than a seqscan.

Isn't that exactly backwards?

regards, tom lane

Louis-David Mitterrand <vindex@apartia.org> writes:

While trying to optimise a query I found that running VACUUM ANALYSE
changed all the Index Scans to Seq Scans and that the only way to revert
to Index Scans was the add "enable_seqscan = 0" in postgresql.conf.

EXPLAIN ANALYZE output would be more interesting than just EXPLAIN.
Also, what does the pg_stats view show for these tables?

Thanks, pg_stats output is rather big so I attached it in a separate
file. Here are the EXPLAIN ANALYZE ouputs:

Tell you the truth, I'm having a real hard time getting excited over
a bug report that says the planner chose a plan taking 10.90 seconds
in preference to one taking 7.96 seconds.

Any time the planner's estimates are within a factor of 2 of reality,
I figure it's done very well. The inherent unknowns are so large that
that really amounts to divination. We can't expect to choose a perfect
plan every time --- if we can avoid choosing a truly stupid plan (say,
one that takes a couple orders of magnitude more time than the best
possible plan) then we ought to be happy.

But having said that, it would be interesting to see if adjusting some
of the planner cost parameters would yield better results in your
situation. The coarsest of these is random_page_cost, which is
presently 4.0 by default. Although I have done some moderately
extensive measurements to get that figure, other folks have reported
that lower numbers like 3.0 or even less seem to suit their platforms
better. In general a lower random_page_cost will favor indexscans...

regards, tom lane

Let me add people's expections of the optimizer and the "it isn't using
the index" questions are getting very old. I have beefed up the FAQ
item on this a month ago, but that hasn't reduced the number of
questions. I almost want to require people to read a specific FAQ item
4.8 before we will reply to anything.

Maybe that FAQ item needs more info. Tom can't be running around trying
to check all these optimizer reports when >90% are just people not
understanding the basics of optimization or query performance.

Maybe we need an optimizer FAQ that will answer the basic questions for
people.

---------------------------------------------------------------------------

Tom Lane wrote:

Louis-David Mitterrand <vindex@apartia.org> writes:

While trying to optimise a query I found that running VACUUM ANALYSE
changed all the Index Scans to Seq Scans and that the only way to revert
to Index Scans was the add "enable_seqscan = 0" in postgresql.conf.

EXPLAIN ANALYZE output would be more interesting than just EXPLAIN.
Also, what does the pg_stats view show for these tables?

Thanks, pg_stats output is rather big so I attached it in a separate
file. Here are the EXPLAIN ANALYZE ouputs:

Tell you the truth, I'm having a real hard time getting excited over
a bug report that says the planner chose a plan taking 10.90 seconds
in preference to one taking 7.96 seconds.

Any time the planner's estimates are within a factor of 2 of reality,
I figure it's done very well. The inherent unknowns are so large that
that really amounts to divination. We can't expect to choose a perfect
plan every time --- if we can avoid choosing a truly stupid plan (say,
one that takes a couple orders of magnitude more time than the best
possible plan) then we ought to be happy.

But having said that, it would be interesting to see if adjusting some
of the planner cost parameters would yield better results in your
situation. The coarsest of these is random_page_cost, which is
presently 4.0 by default. Although I have done some moderately
extensive measurements to get that figure, other folks have reported
that lower numbers like 3.0 or even less seem to suit their platforms
better. In general a lower random_page_cost will favor indexscans...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian wrote:

Let me add people's expections of the optimizer and the "it isn't using
the index" questions are getting very old. I have beefed up the FAQ
item on this a month ago, but that hasn't reduced the number of
questions. I almost want to require people to read a specific FAQ item
4.8 before we will reply to anything.

Maybe that FAQ item needs more info. Tom can't be running around trying
to check all these optimizer reports when >90% are just people not
understanding the basics of optimization or query performance.

Maybe we need an optimizer FAQ that will answer the basic questions for
people.

I think you are missing a huge point, people are confused by the operation of
PostgreSQL. You admit that there are a lot of questions about this topic. This
means that something is happening which is non-intuitive. Bruce, you are an
expert in PostgreSQL, but most people who use it are not. The unexpected
behavior is just that, unexpected, or a surprise.

Business people, accountants, and engineers do not like surprises. PostgreSQL's
behavior on index usage is totally confusing. If I can paraphase correctly,
PostgreSQL wants to have a good reason to use an index. Most people expect a
database to have an undeniable reason NOT to use an index. I would also say, if
a DBA created an index, there is a strong indication that there is a need for
one! (DBA knowledge vs statistics)

That is the difference, in another post Tom said he could not get excited about
10.9 second execution time over a 7.96 execution time. Damn!!! I would. That is
wrong.

I have bitched about the index stuff for a while, and always have bumped up
against this problem. If I can sway anyone's opinion, I would say, unless
(using Tom's words) a "factor of 2" planner difference against, I would use an
index. Rather than needing clear evidence to use an index, I would say you need
clear evidence not too.

mlw <markw@mohawksoft.com> writes:

That is the difference, in another post Tom said he could not get
excited about 10.9 second execution time over a 7.96 execution
time. Damn!!! I would. That is wrong.

Sure. Show us how to make the planner's estimates 2x more accurate
(on average) than they are now, and I'll get excited too.

But forcing indexscan to be chosen over seqscan does not count as
making it more accurate. (If you think it does, then you don't
need to be in this thread at all; set enable_seqscan = 0 and
stop bugging us ;-))

regards, tom lane

I have bitched about the index stuff for a while, and always have
bumped up
against this problem. If I can sway anyone's opinion, I would say, unless
(using Tom's words) a "factor of 2" planner difference against, I
would use an
index. Rather than needing clear evidence to use an index, I
would say you need
clear evidence not too.

I spend a lot of time answering questions on various database forums and I
find that the single thing that most newbies just cannot understand is that
a sequential scan is often a lot faster than an index scan. They just
cannot comprehend that an index can be slower. Ever. For any query. That
is not our problem...

What we could offer tho, is more manual control over the planner. People
can do this to a mild extend by disabling sequential scans, but it looks
like it should be extended...

Chris

Tom Lane wrote:

mlw <markw@mohawksoft.com> writes:

That is the difference, in another post Tom said he could not get
excited about 10.9 second execution time over a 7.96 execution
time. Damn!!! I would. That is wrong.

Sure. Show us how to make the planner's estimates 2x more accurate
(on average) than they are now, and I'll get excited too.

But forcing indexscan to be chosen over seqscan does not count as
making it more accurate. (If you think it does, then you don't
need to be in this thread at all; set enable_seqscan = 0 and
stop bugging us ;-))

Oh, come on Tom, surely I have been around long enough to lend credence that
wish to have a positive affect on PostgreSQL development.

enable_seqscan=0, disallows sequential scan, that is not what I am saying. This
is a problem I (and others) have been yapping about for a long time.

(Please remember, I USE PostgreSQL, I have a vested interest in it being the
best RDBMS available.)

I just think there is sufficient evidence to suggest that if a DBA creates an
index, there is strong evidence (better than statistics) that the index need be
used. In the event that an index exists, there is a strong indication that,
without overwhelming evidence, that the index should be used. You have admitted
that statistics suck, but the existence of an index must weight (heavily) on
the evaluation on whether or not to use an index.

Christopher Kings-Lynne wrote:

I have bitched about the index stuff for a while, and always have
bumped up
against this problem. If I can sway anyone's opinion, I would say, unless
(using Tom's words) a "factor of 2" planner difference against, I
would use an
index. Rather than needing clear evidence to use an index, I
would say you need
clear evidence not too.

I spend a lot of time answering questions on various database forums and I
find that the single thing that most newbies just cannot understand is that
a sequential scan is often a lot faster than an index scan. They just
cannot comprehend that an index can be slower. Ever. For any query. That
is not our problem...

Here is the problem, in a single paragraph.

If the DBA notices that there is a problem with a query, he adds an index, he
notices that there is no difference, then he notices that PostgreSQL is not
using his index. First and foremost he gets mad at PostgreSQL for not using his
index. If PostgreSQL decided to use an index which increases execution time,
the DBA would delete the index. If PostgreSQL does not use an index, he has to
modify the posgresql.conf file, which disallows PostgreSQL from using an index
when it would be a clear loser.

My assertion is this: "If a DBA creates an index, he has a basis for his
actions."

On Wed, 2002-04-17 at 06:51, mlw wrote:

I just think there is sufficient evidence to suggest that if a DBA creates an
index, there is strong evidence (better than statistics) that the index need be
used. In the event that an index exists, there is a strong indication that,
without overwhelming evidence, that the index should be used. You have admitted
that statistics suck, but the existence of an index must weight (heavily) on
the evaluation on whether or not to use an index.

But indexes are not, for the most part, there because of a specific
choice to have an index, but as the implementation of PRIMARY KEY and
UNIQUE. Therefore the main part of your argument fails.

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C

"But as many as received him, to them gave he power to
become the sons of God, even to them that believe on
his name." John 1:12

On Wed, 2002-04-17 at 06:51, mlw wrote:

I just think there is sufficient evidence to suggest that if a DBA creates an
index, there is strong evidence (better than statistics) that the index need be
used. In the event that an index exists, there is a strong indication that,
without overwhelming evidence, that the index should be used. You have admitted
that statistics suck, but the existence of an index must weight (heavily) on
the evaluation on whether or not to use an index.

But indexes are not, for the most part, there because of a specific
choice to have an index, but as the implementation of PRIMARY KEY and
UNIQUE. Therefore the main part of your argument fails.

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C

"But as many as received him, to them gave he power to
become the sons of God, even to them that believe on
his name." John 1:12

Oliver Elphick wrote:

On Wed, 2002-04-17 at 06:51, mlw wrote:

I just think there is sufficient evidence to suggest that if a DBA creates an
index, there is strong evidence (better than statistics) that the index need be
used. In the event that an index exists, there is a strong indication that,
without overwhelming evidence, that the index should be used. You have admitted
that statistics suck, but the existence of an index must weight (heavily) on
the evaluation on whether or not to use an index.

But indexes are not, for the most part, there because of a specific
choice to have an index, but as the implementation of PRIMARY KEY and
UNIQUE. Therefore the main part of your argument fails.

Let's talk about the primary key, that will not exhibit the borderline behavior
that we see. I have had first hand experience (and frustration) on PostgreSQL's
choice of using an index.

The primary key and UNIQUE constraint will only exhibit reduced performance on
REALLY small tables, in which case, the reduced performance is minimal if not
nonexistent.

On Wed, 2002-04-17 at 11:00, mlw wrote:

Here is the problem, in a single paragraph.

If the DBA notices that there is a problem with a query, he adds an index, he
notices that there is no difference, then he notices that PostgreSQL is not
using his index. First and foremost he gets mad at PostgreSQL for not using his
index.

Perhaps a notice from backend:

NOTICE: I see the DBA has created a useless index ...

;)

Or would this make the DBA even madder ;) ;)

If PostgreSQL decided to use an index which increases execution time,
the DBA would delete the index. If PostgreSQL does not use an index, he has to
modify the posgresql.conf file,

Or just do

set enable_seqscan to off;

which disallows PostgreSQL from using an index when it would be a clear loser.

My assertion is this: "If a DBA creates an index, he has a basis for his
actions."

The basis can be that "his boss told him to" ?

------------------
Hannu

If the DBA notices that there is a problem with a query, he adds
an index, he
notices that there is no difference, then he notices that
PostgreSQL is not
using his index. First and foremost he gets mad at PostgreSQL for
not using his
index. If PostgreSQL decided to use an index which increases
execution time,
the DBA would delete the index. If PostgreSQL does not use an
index, he has to
modify the posgresql.conf file, which disallows PostgreSQL from
using an index
when it would be a clear loser.

My assertion is this: "If a DBA creates an index, he has a basis for his
actions."

What about a GUC parameter

prefer_indexes = yes/no

Which when set to yes, assumes the DBA knows what he's doing. Unless the
table is really small, in which case it'll still scan.

But then again, if the dba sets up a huge table (million rows) and does a
select over an indexed field that will return 1/6 of all the rows, then
postgres would be nuts to use the index...

But then if the DBA does a query to return just 1 of the rows, postgres
would be nuts NOT to use the index. How do you handle this situation?

Chris