Wrong index choosen?

On Fri, 23 Jul 2004, Gaetano Mendola wrote:

empdb=# explain analyze select * from v_past_connections where login_time > '2004-07-21';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_user_logs_login_time on user_logs (cost=0.00..12.90 rows=481 width=28) (actual time=7.338..661.300 rows=22477 loops=1)
Index Cond: (login_time > '2004-07-21 00:00:00+02'::timestamp with time zone)
Total runtime: 676.472 ms
(3 rows)

In this plan it estimates to get 481 but it got 22477. So the estimation
was very wrong. You can increase the statistics tarhet on the login_time
and it will probably be better (after the next analyze).

why then the planner choose to do an index scan using the filter that
retrieve a bigger ammount of rows ? A bug ?

Because it has to decide on the plan before it knows exactly what the
result will be. As seen above, the estimation was wrong and thus the plan
was not as good as it could have been.

In this case you probably also want to create a combined index on both
columns:

CREATE INDEX foo ON user_log (id_user, login_time);

ps. This letter belonged to pgsql-performance and not pgsql-hackers.

--
/Dennis Bj�rklund

Dennis Bjorklund <db@zigo.dhs.org> writes:

In this plan it estimates to get 481 but it got 22477. So the estimation
was very wrong. You can increase the statistics tarhet on the login_time
and it will probably be better (after the next analyze).

Given the nature of the data (login times), I'd imagine that the problem
is simply that he hasn't analyzed recently enough. A bump in stats
target may not be needed, but he's going to have to re-analyze that
column often if he wants this sort of query to be estimated accurately,
because the fraction of entries later than a given time T is *always*
going to be changing.

regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Tom Lane wrote:

| Dennis Bjorklund <db@zigo.dhs.org> writes:
|
|>In this plan it estimates to get 481 but it got 22477. So the estimation
|>was very wrong. You can increase the statistics tarhet on the login_time
|>and it will probably be better (after the next analyze).
|
|
| Given the nature of the data (login times), I'd imagine that the problem
| is simply that he hasn't analyzed recently enough. A bump in stats
| target may not be needed, but he's going to have to re-analyze that
| column often if he wants this sort of query to be estimated accurately,
| because the fraction of entries later than a given time T is *always*
| going to be changing.

Well know that I think about it, I felt my shoulders covered by
pg_autovacuum but looking at the log I see that table never analyzed!
Aaargh.

I already applied the patch for the autovacuum but evidently I have to
make it more aggressive, I'm sorry that I can not made him more aggressive
only for this table.

Thank you all.

Regards
Gaetano Mendola

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFBAU3g7UpzwH2SGd4RAhbEAKDLbKXLGRqphBbfyBh6cu7QoqFQhACfdDtu
cGS0K1UuTuwTDp4P2JjQ30A=
=aepf
-----END PGP SIGNATURE-----

Gaetano Mendola wrote:

Tom Lane wrote:
| Given the nature of the data (login times), I'd imagine that the problem
| is simply that he hasn't analyzed recently enough. A bump in stats
| target may not be needed, but he's going to have to re-analyze that
| column often if he wants this sort of query to be estimated accurately,
| because the fraction of entries later than a given time T is *always*
| going to be changing.

Well know that I think about it, I felt my shoulders covered by
pg_autovacuum but looking at the log I see that table never analyzed!
Aaargh.

I already applied the patch for the autovacuum but evidently I have to
make it more aggressive, I'm sorry that I can not made him more aggressive
only for this table.

Yeah, the version of autovacuum in 7.4 contrib doesn't allow table
specific settings. The patch I have sumbitted for 7.5 does, so
hopefully this will be better in the future.

You can however set the VACUUM and ANALYZE thresholds independently.
So perhpaps it will help you if you set your ANALYZE setting to be very
aggressive and your VACUUM settings to something more standard.

Matthew

Hi all,

just as a question.

There will be some day a feature that let you force
the planner to use an specific index, like oracle
does?

Of course the planner is smart enough most times but
sometimes such an option would be usefull, don't you
think so?

Thanx in advance,
Jaime Casanova

_________________________________________________________
Do You Yahoo!?
Informaci�n de Estados Unidos y Am�rica Latina, en Yahoo! Noticias.
Vis�tanos en http://noticias.espanol.yahoo.com

On Fri, 2004-07-23 at 15:51, Jaime Casanova wrote:

Hi all,

just as a question.

There will be some day a feature that let you force
the planner to use an specific index, like oracle
does?

Of course the planner is smart enough most times but
sometimes such an option would be usefull, don't you
think so?

A planner that always made the right choice would be the most useful
thing. After that, the ability to "push" the planner towards an index
would be pretty nice.

Adding features that make PostgreSQL more error prone (i.e. forcing
particular index usage, etc.) and harder to drive but allow an expert to
get what they want is kind of a dangerous road to tread.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Matthew T. O'Connor wrote:

| Gaetano Mendola wrote:
|
|> Tom Lane wrote:
|> | Given the nature of the data (login times), I'd imagine that the
|> problem
|> | is simply that he hasn't analyzed recently enough. A bump in stats
|> | target may not be needed, but he's going to have to re-analyze that
|> | column often if he wants this sort of query to be estimated accurately,
|> | because the fraction of entries later than a given time T is *always*
|> | going to be changing.
|>
|> Well know that I think about it, I felt my shoulders covered by
|> pg_autovacuum but looking at the log I see that table never analyzed!
|> Aaargh.
|>
|> I already applied the patch for the autovacuum but evidently I have to
|> make it more aggressive, I'm sorry that I can not made him more
|> aggressive
|> only for this table.
|
|
| Yeah, the version of autovacuum in 7.4 contrib doesn't allow table
| specific settings. The patch I have sumbitted for 7.5 does, so
| hopefully this will be better in the future.
|
| You can however set the VACUUM and ANALYZE thresholds independently. So
| perhpaps it will help you if you set your ANALYZE setting to be very
| aggressive and your VACUUM settings to something more standard.

Well I think pg_autovacuum as is in 7.4 can not help me for this particular
table.

The table have 4.8 milions rows and I have for that table almost 10252 new
entries for day.

I'm using pg_autovacuum with -a 200 -A 0.8 this means a threashold for
that table equal to: 3849008 and if I understod well the way pg_autovacuum
works this means have an analyze each 375 days, and I need an analyze for
each day, at least.

So I think is better for me put an analyze for that table in the cron.

Am I wrong ?

Regards
Gaetano Mendola

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFBAag87UpzwH2SGd4RAqb1AJ416ioVEY5T/dqnAQsaaqqoWcU3ZACghzsO
4xMowWp/MM8+i7DhoRO4018=
=/gNn
-----END PGP SIGNATURE-----

Gaetano Mendola wrote:

Well I think pg_autovacuum as is in 7.4 can not help me for this particular
table.

The table have 4.8 milions rows and I have for that table almost 10252 new
entries for day.

I'm using pg_autovacuum with -a 200 -A 0.8 this means a threashold for
that table equal to: 3849008 and if I understod well the way pg_autovacuum
works this means have an analyze each 375 days, and I need an analyze for
each day, at least.

So I think is better for me put an analyze for that table in the cron.

Am I wrong ?

No, I think you are right. You could do something like -a 1000 -A
.00185, but that will probably for an analyze too often for most of your
other tables.