From: | Stephen Friedrich <stephen(dot)friedrich(at)fortis-it(dot)de> |
---|---|
To: | Richard Huxton <dev(at)archonet(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Partitioning: Planner makes no use of indexes on inherited |
Date: | 2006-01-18 10:05:24 |
Message-ID: | 43CE12E4.2000405@fortis-it.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> Hmm - looking at it, this seems the case. Directly querying the
> partition it uses the index but estimates a cost of:
> cost=0.00..23510.68 rows=575532 width=8907)
> The seq-scan says it has a cost of:
> cost=0.00..20737.15 rows=575532 width=8907
>
> Could you issue "set enable_seqscan=off" and then show explain analyse
> on the second query again?
That doesn't make a big difference:
Limit (cost=23365451.30..23365453.80 rows=1000 width=13436)
-> Sort (cost=23365451.30..23366890.13 rows=575533 width=13436)
Sort Key: this_.id
-> Result (cost=0.00..23313.51 rows=575533 width=13436)
-> Append (cost=0.00..23313.51 rows=575533 width=13436)
-> Index Scan using call_source_cdr_index on call_sources this_ (cost=0.00..4.82 rows=1 width=13436)
Index Cond: (cdr_id = 10554)
-> Index Scan using call_source_10554_cdr_index on call_sources_10554 this_ (cost=0.00..23308.68 rows=575532 width=8907)
Index Cond: (cdr_id = 10554)
IMHO the problem is that this should not be done:
-> Sort (cost=23365451.30..23366890.13 rows=575533 width=13436)
Sort Key: this_.id
because there is an index on the id column of the inherited table, so it
should be used as it is when querying the inherited table directly:
-> Index Scan using call_source_10554_id_index on call_sources_10554 this_ (cost=0.00..23510.68 rows=575532 width=8907)
Filter: (cdr_id = 10554)
From | Date | Subject | |
---|---|---|---|
Next Message | Martijn van Oosterhout | 2006-01-18 10:20:26 | Re: Partitioning: Planner makes no use of indexes on inherited |
Previous Message | Kai Hessing | 2006-01-18 09:51:12 | Re: [GENERAL] Problem with restoring database from 7.3.1 to 8.0.1 |