Re: Failure to use indexes (fwd)

The solution to my problem was to increase statistics
value and do another analyze. You can also change
default_statistics_target parameter in
postgresql.conf. Don't know if that's related to the
problem you're seeing, but it's worth a try.

Eugene

--- Edmund Dengler <edmundd(at)eSentire(dot)com> wrote:

> Greetings all!
>
> Given the quiet, I assume that there is no
> experience with index issues on
> inherited tables? Just seeing if anybody may have
> any ideas or suggested
> work arounds (I seem to have found one by
> constructing a query that does
> all the joins between inherited tables explicitely -
> this causes the
> indexes to be used - still trying to make sure it is
> a legitimate method).
>
> Regards!
> Ed
>
> ---------- Forwarded message ----------
> Date: Fri, 29 Jul 2005 14:23:29 -0400 (EDT)
> From: Edmund Dengler <edmundd(at)eSentire(dot)com>
> To: Postgresql-General
> <pgsql-general(at)postgresql(dot)org>
> Subject: Re: [GENERAL] Failure to use indexes
>
> Greetings!
>
> I managed to get an even simpler example:
>
> select *
> from eventlog.record_main
> where luid in (
> select luid from
> eventlog_partition._20050706__raw_record
> order by luid limit 1
> )
> ;
>
> If I use <=> rather than <in>, postgresql uses index
> scanning. As soon as
> I use <in> (ie, a set to compare against), I get
> sequential scanning,
> event though the set size is only a single element.
>
> Regards!
> Ed
>
>
> On Fri, 29 Jul 2005, Edmund Dengler wrote:
>
> > Greetings!
> >
> > I am using <inherits> to partition several tables.
> When I perform a query
> > on another table, and then try to join against an
> inherited table set, the
> > optimizer does not use any indexes to perform the
> join.
> >
> > This is 8.0.1. Is this fixed in 8.0.2 or 8.0.3?
> >
> > The query:
> >
> > explain
> > select *
> > from (
> > select * from eventlog.record_classification
> as record_classification
> > where
> > time_written >= '2005-07-06
> 00:00:00+00'::timestamptz
> > and time_written < '2005-07-06
> 00:00:00+00'::timestamptz
> > order by time_written, luid
> > offset 0
> > limit 500
> > ) as classification
> > join eventlog.record_main as main using (luid,
> time_written)
> > ;
> >
> > The explanation:
> >
> >
> QUERY
> PLAN
> >
>
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> > Hash Join (cost=51.15..20191003.89 rows=208027
> width=178)
> > Hash Cond: (("outer".luid = "inner".luid) AND
> ("outer".time_written = "inner".time_written))
> > -> Append (cost=0.00..14641488.64
> rows=554738383 width=96)
> > -> Seq Scan on record_main main
> (cost=0.00..0.00 rows=1 width=96)
> > -> Seq Scan on _20050723__record_main
> main (cost=0.00..94078.62 rows=3564462 width=96)
> > -> Seq Scan on _20050724__record_main
> main (cost=0.00..110075.12 rows=4170512 width=96)
> > -> Seq Scan on _20050725__record_main
> main (cost=0.00..122836.02 rows=4654002 width=96)
> > -> Seq Scan on _20050726__record_main
> main (cost=0.00..142347.71 rows=5393271 width=96)
> > -> Seq Scan on _20050727__record_main
> main (cost=0.00..130858.80 rows=4957980 width=96)
> > ....
> > (and so on, currently 123 such inheritd
> tables)
> > ....
> > -> Hash (cost=51.07..51.07 rows=15 width=98)
> > -> Subquery Scan classification
> (cost=50.89..51.07 rows=15 width=98)
> > -> Limit (cost=50.89..50.92
> rows=15 width=98)
> > -> Sort (cost=50.89..50.92
> rows=15 width=98)
> > Sort Key:
> record_classification.time_written,
> record_classification.luid
> > -> Result
> (cost=0.00..50.59 rows=15 width=98)
> > -> Append
> (cost=0.00..50.59 rows=15 width=98)
> > -> Seq
> Scan on record_classification (cost=0.00..0.00
> rows=1 width=98)
> >
> Filter: ((time_written >= '2005-07-06
> 00:00:00+00'::timestamp with time zone) AND
> (time_written < '2005-07-06 00:00:00+00'::timestamp
> with time zone))
> > -> Index
> Scan using
> _20050705__record_classification_time_written_idx on
> _20050705__record_classification
> record_classification (cost=0.00..3.46 rows=1
> width=54)
> > Index
> Cond: ((time_written >= '2005-07-06
> 00:00:00+00'::timestamp with time zone) AND
> (time_written < '2005-07-06 00:00:00+00'::timestamp
> with time zone))
> > -> Index
> Scan using
> _20050701__record_classification_time_written_idx on
> _20050701__record_classification
> record_classification (cost=0.00..3.59 rows=1
> width=54)
> > Index
> Cond: ((time_written >= '2005-07-06
> 00:00:00+00'::timestamp with time zone) AND
> (time_written < '2005-07-06 00:00:00+00'::timestamp
> with time zone))
> > -> Index
> Scan using
> _20050702__record_classification_time_written_idx on
> _20050702__record_classification
> record_classification (cost=0.00..3.69 rows=1
> width=54)
> > Index
> Cond: ((time_written >= '2005-07-06
> 00:00:00+00'::timestamp with time zone) AND
> (time_written < '2005-07-06 00:00:00+00'::timestamp
> with time zone))
> > -> Index
> Scan using
> _20050703__record_classification_time_written_idx on
> _20050703__record_classification
> record_classification (cost=0.00..3.70 rows=1
> width=54)
> > Index
> Cond: ((time_written >= '2005-07-06
> 00:00:00+00'::timestamp with time zone) AND
> (time_written < '2005-07-06 00:00:00+00'::timestamp
> with time zone))
> > ....
> > (and so on)
> > ....
> > -> Index
> Scan using
> _20050714__record_classification_time_written_idx on
> _20050714__record_classification
> record_classification (cost=0.00..3.69 rows=1
> width=53)
> > Index
> Cond: ((time_written >= '2005-07-06
> 00:00:00+00'::timestamp with time zone) AND
> (time_written < '2005-07-06 00:00:00+00'::timestamp
> with time zone))
> > (164 rows)
> >
> > Sample tables:
> >
> > eventlog=# \d
> eventlog_partition._20050723__record_main
> > Table
> "eventlog_partition._20050723__record_main"
> > Column | Type |
> Modifiers
> >
>
----------------------+--------------------------+-----------
> > luid | bigint |
> not
=== message truncated ===

____________________________________________________
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs