Обсуждение: JOIN with inherited table ignores indexes

I have this table setup on a 8.1.4 server:

pj_info_attach(attachment_nr, some more cols) -- index, 50k rows
pj_info_attach_compressable() INHERITS (pj_info_attach) -- index, 1M rows
pj_info_attach_not_compressable()  INHERITS (pj_info_attach) -- index, 0
rows

EXPLAIN ANALYZE SELECT aes FROM pj_info_attach
 WHERE attachment_nr in (.. 20 numeric key values.. )
yields a big bitmap index scan plan, 1.8ms total runtime, that's fine.

Using a subselect on zz_attachment_graustufentest, which has 20 rows of
exactly the key values entered manually in the query above:

EXPLAIN ANALYZE SELECT aes FROM pj_info_attach
 WHERE attachment_nr in
(SELECT attachment_nr FROM zz_attachment_graustufentest)
gives 49s runtime, and full table scans.

Merge Join  (cost=158472.98..164927.22 rows=107569 width=8)
                    (actual time=49714.702..49715.142 rows=20 loops=1)
  Merge Cond: ("outer"."?column2?" = "inner"."?column3?")
  ->  Sort  (cost=2.16..2.21 rows=20 width=13)(actual time=0.752..0.830
rows=20 loops=1)
        Sort Key: (zz_attachment_graustufentest.attachment_nr)::numeric
        ->  Result  (cost=1.63..1.73 rows=20 width=13) (actual
time=0.220..0.637 rows=20 loops=1)
              ->  Unique  (cost=1.63..1.73 rows=20 width=13) (actual
time=0.210..0.459 rows=20 loops=1)
                    ->  Sort  (cost=1.63..1.68 rows=20 width=13) (actual
time=0.202..0.281 rows=20 loops=1)
                          Sort Key:
zz_attachment_graustufentest.attachment_nr
                          ->  Seq Scan on zz_attachment_graustufentest
(cost=0.00..1.20 rows=20 width=13)

(actual time=0.007..0.092 rows=20 loops=1)
  ->  Sort  (cost=158470.81..161160.04 rows=1075690 width=40)
                (actual time=44705.196..47222.685 rows=589842 loops=1)
        Sort Key: (public.pj_info_attach.attachment_nr)::numeric
        ->  Result  (cost=0.00..32736.90 rows=1075690 width=40)
                         (actual time=0.023..21958.761 rows=1074930 loops=1)
              ->  Append  (cost=0.00..32736.90 rows=1075690 width=40)
                                  (actual time=0.015..13485.153
rows=1074930 loops=1)
                    ->  Seq Scan on pj_info_attach  (cost=0.00..1433.57
rows=49957 width=21)

(actual time=0.008..214.308 rows=49957 loops=1)
                    ->  Seq Scan on pj_info_attach_compressable
pj_info_attach  (cost=0.00..31285.73 rows=1024973 width=21)

(actual time=0.032..4812.090 rows=1024973 loops=1)
                    ->  Seq Scan on pj_info_attach_not_compressable
pj_info_attach  (cost=0.00..17.60 rows=760 width=40)

(actual time=0.005..0.005 rows=0 loops=1)
Total runtime: 49747.630 ms

Any explanation for this horror?

Regards,
Andreas

On Thu, Jun 08, 2006 at 01:40:33PM +0200, Andreas Pflug wrote:
> I have this table setup on a 8.1.4 server:
>
> pj_info_attach(attachment_nr, some more cols) -- index, 50k rows
> pj_info_attach_compressable() INHERITS (pj_info_attach) -- index, 1M rows
> pj_info_attach_not_compressable()  INHERITS (pj_info_attach) -- index, 0
> rows
>
> EXPLAIN ANALYZE SELECT aes FROM pj_info_attach
> WHERE attachment_nr in (.. 20 numeric key values.. )
> yields a big bitmap index scan plan, 1.8ms total runtime, that's fine.
>
> Using a subselect on zz_attachment_graustufentest, which has 20 rows of
> exactly the key values entered manually in the query above:

I'm pretty sure the issue is that the planner doesn't know what values
will be coming back from the subselect at plan time, so if the
distribution of values in attachment_nr isn't fairly constant you can g
et some pretty bad plans. Unfortunately, no one's figured out a good way
to fix this yet.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Andreas Pflug <pgadmin@pse-consulting.de> writes:
> Any explanation for this horror?

Existing releases aren't smart about planning joins to inheritance
trees.  CVS HEAD is better...

            regards, tom lane

Tom Lane wrote:
> Andreas Pflug <pgadmin@pse-consulting.de> writes:
>
>>Any explanation for this horror?
>
>
> Existing releases aren't smart about planning joins to inheritance
> trees.

Using a view that UNIONs SELECT .. ONLY as replacement for the parent
table isn't any better. Is that improved too?

>  CVS HEAD is better...

Customers like HEAD versions for production purposes :-)

Regards,
Andreas