pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Wrong index choosen?
9+ messages / 6 participants
[nested] [flat]

* Wrong index choosen?
@ 2004-07-23 08:26  Gaetano Mendola <mendola@bigfoot.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Gaetano Mendola @ 2004-07-23 08:26 UTC (permalink / raw)
  To: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>

I hall
I have a query in this form:

empdb=# explain analyze select * from v_past_connections where id_user = 26195 and login_time > '2004-07-21';
                                                                QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
  Index Scan using idx_user_logs_login_time on user_logs  (cost=0.00..14.10 rows=1 width=28) (actual time=66.890..198.998 rows=5 loops=1)
    Index Cond: (login_time > '2004-07-21 00:00:00+02'::timestamp with time zone)
    Filter: (id_user = 26195)
  Total runtime: 199.083 ms
(4 rows)


as you see the index on the time stamp column is used

The table have indexes on both columns:

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)

empdb=# explain analyze select * from v_past_connections where id_user = 26195;
                                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
  Index Scan using idx_user_user_logs on user_logs  (cost=0.00..252.47 rows=320 width=28) (actual time=4.420..100.122 rows=221 loops=1)
    Index Cond: (id_user = 26195)
  Total runtime: 100.348 ms
(3 rows)


The rows filtered out with both condictions are two order of magnitude differents,
also the extimated rows are close to real numbers:


empdb=# select count(*) from v_past_connections where id_user = 26195;
  count
-------
    221
(1 row)

empdb=# select count(*) from v_past_connections where login_time > '2004-07-21';
  count
-------
  22441
(1 row)


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





Regards
Gaetano Mendola


















^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Wrong index choosen?
@ 2004-07-23 12:01  Dennis Bjorklund <db@zigo.dhs.org>
  parent: Gaetano Mendola <mendola@bigfoot.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Dennis Bjorklund @ 2004-07-23 12:01 UTC (permalink / raw)
  To: Gaetano Mendola <mendola@bigfoot.com>; +Cc: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>

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




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Wrong index choosen?
@ 2004-07-23 14:45  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Dennis Bjorklund <db@zigo.dhs.org>
  0 siblings, 1 reply; 9+ messages in thread

From: Tom Lane @ 2004-07-23 14:45 UTC (permalink / raw)
  To: Dennis Bjorklund <db@zigo.dhs.org>; +Cc: Gaetano Mendola <mendola@bigfoot.com>; pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>

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



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [HACKERS] Wrong index choosen?
@ 2004-07-23 17:41  Gaetano Mendola <mendola@bigfoot.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 9+ messages in thread

From: Gaetano Mendola @ 2004-07-23 17:41 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Dennis Bjorklund <db@zigo.dhs.org>; pgsql-performance

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




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [HACKERS] Wrong index choosen?
@ 2004-07-23 18:12  Matthew T. O'Connor <matthew@zeut.net>
  parent: Gaetano Mendola <mendola@bigfoot.com>
  0 siblings, 2 replies; 9+ messages in thread

From: Matthew T. O'Connor @ 2004-07-23 18:12 UTC (permalink / raw)
  To: Gaetano Mendola <mendola@bigfoot.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Dennis Bjorklund <db@zigo.dhs.org>; pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [HACKERS] Wrong index choosen?
@ 2004-07-23 21:51  Jaime Casanova <systemguards@yahoo.com>
  parent: Matthew T. O'Connor <matthew@zeut.net>
  1 sibling, 1 reply; 9+ messages in thread

From: Jaime Casanova @ 2004-07-23 21:51 UTC (permalink / raw)
  To: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [HACKERS] Wrong index choosen?
@ 2004-07-23 22:02  Scott Marlowe <smarlowe@qwest.net>
  parent: Jaime Casanova <systemguards@yahoo.com>
  0 siblings, 0 replies; 9+ messages in thread

From: Scott Marlowe @ 2004-07-23 22:02 UTC (permalink / raw)
  To: Jaime Casanova <systemguards@yahoo.com>; +Cc: pgsql-performance

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.




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [HACKERS] Wrong index choosen?
@ 2004-07-24 00:07  Gaetano Mendola <mendola@bigfoot.com>
  parent: Matthew T. O'Connor <matthew@zeut.net>
  1 sibling, 1 reply; 9+ messages in thread

From: Gaetano Mendola @ 2004-07-24 00:07 UTC (permalink / raw)
  To: Matthew T. O'Connor <matthew@zeut.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Dennis Bjorklund <db@zigo.dhs.org>; pgsql-performance

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




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [HACKERS] Wrong index choosen?
@ 2004-07-24 01:49  Matthew T. O'Connor <matthew@zeut.net>
  parent: Gaetano Mendola <mendola@bigfoot.com>
  0 siblings, 0 replies; 9+ messages in thread

From: Matthew T. O'Connor @ 2004-07-24 01:49 UTC (permalink / raw)
  To: Gaetano Mendola <mendola@bigfoot.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Dennis Bjorklund <db@zigo.dhs.org>; pgsql-performance

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.





^ permalink  raw  reply  [nested|flat] 9+ messages in thread


end of thread, other threads:[~2004-07-24 01:49 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-07-23 08:26 Wrong index choosen? Gaetano Mendola <mendola@bigfoot.com>
2004-07-23 12:01 ` Dennis Bjorklund <db@zigo.dhs.org>
2004-07-23 14:45   ` Tom Lane <tgl@sss.pgh.pa.us>
2004-07-23 17:41     ` Gaetano Mendola <mendola@bigfoot.com>
2004-07-23 18:12       ` Matthew T. O'Connor <matthew@zeut.net>
2004-07-23 21:51         ` Jaime Casanova <systemguards@yahoo.com>
2004-07-23 22:02           ` Scott Marlowe <smarlowe@qwest.net>
2004-07-24 00:07         ` Gaetano Mendola <mendola@bigfoot.com>
2004-07-24 01:49           ` Matthew T. O'Connor <matthew@zeut.net>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox