500x speed-down: Wrong query plan? - Mailing list pgsql-performance

Hello gentlemen,

Although this is my first post on the list, I am a fairly experienced PostgreSQL
programmer. I am writing an ERP application suite using PostgreSQL as the
preferred DBMS. Let me state that the SQL DDL is automatically generated by a
CASE tool from an ER model. The generated schema contains the appropriate
primary key and foreign key constraints, as defined by the original ER model, as
well as "reverse indexes" on foreign keys, allowing (in theory) rapid backward
navigation of foreign keys in joins.

Let me show a sample join of two tables in the database schema. The information
provided is quite extensive. I'm sorry for that, but I think it is better to
provide the list with all the relevant information.

Package: postgresql-7.4
Priority: optional
Section: misc
Installed-Size: 7860
Maintainer: Martin Pitt <mpitt@debian.org>
Architecture: i386
Version: 1:7.4.9-2



                                            Table "public.articolo"
          Column          |            Type             |
Modifiers
-------------------------+-----------------------------+-----------------------------------------------------
  bigoid                  | bigint                      | not null default
nextval('object_bigoid_seq'::text)
  metadata                | text                        |
  finalized               | timestamp without time zone |
  xdbs_created            | timestamp without time zone | default now()
  xdbs_modified           | timestamp without time zone |
  id_ente                 | text                        | not null
  barcode                 | text                        |
  tipo                    | text                        |
  id_produttore           | text                        | not null
  id_articolo             | text                        | not null
  venditore_id_ente       | text                        |
  id_prodotto             | text                        |
  aggregato_id_ente       | text                        |
  aggregato_id_produttore | text                        |
  aggregato_id_articolo   | text                        |
  descr                   | text                        |
  url                     | text                        |
  datasheet               | text                        |
  scheda_sicurezza        | text                        |
  peso                    | numeric                     |
  lunghezza               | numeric                     |
  larghezza               | numeric                     |
  altezza                 | numeric                     |
  volume                  | numeric                     |
  max_strati              | numeric                     |
  um                      | text                        |
Indexes:
     "articolo_pkey" primary key, btree (id_ente, id_produttore, id_articolo)
     "articolo_unique_barcode_index" unique, btree (barcode)
     "articolo_modified_index" btree (xdbs_modified)
Foreign-key constraints:
     "$4" FOREIGN KEY (um) REFERENCES um(um) DEFERRABLE INITIALLY DEFERRED
     "$3" FOREIGN KEY (aggregato_id_ente, aggregato_id_produttore,
aggregato_id_articolo) REFERENCES articolo(id_ente, id_produttore, id_articolo)
DEFERRABLE INITIALLY DEFERRED
     "$2" FOREIGN KEY (venditore_id_ente, id_prodotto) REFERENCES
prodotto(venditore_id_ente, id_prodotto) DEFERRABLE INITIALLY DEFERRED
     "$1" FOREIGN KEY (id_ente) REFERENCES ente(id_ente) DEFERRABLE INITIALLY
DEFERRED
Rules:
     articolo_delete_rule AS ON DELETE TO articolo DO INSERT INTO articolo_trash
(id_ente, id_produttore, id_articolo, venditore_id_ente, id_prodotto,
aggregato_id_ente, aggregato_id_produttore, aggregato_id_articolo, descr, url,
datasheet, scheda_sicurezza, peso, lunghezza, larghezza, altezza, volume,
max_strati, um, barcode, tipo, bigoid, metadata, finalized, xdbs_created,
xdbs_modified) VALUES (old.id_ente, old.id_produttore, old.id_articolo,
old.venditore_id_ente, old.id_prodotto, old.aggregato_id_ente,
old.aggregato_id_produttore, old.aggregato_id_articolo, old.descr, old.url,
old.datasheet, old.scheda_sicurezza, old.peso, old.lunghezza, old.larghezza,
old.altezza, old.volume, old.max_strati, old.um, old.barcode, old.tipo,
old.bigoid, old.metadata, old.finalized, old.xdbs_created, old.xdbs_modified)
     articolo_update_rule AS ON UPDATE TO articolo WHERE
((new.xdbs_modified)::timestamp with time zone <> now()) DO INSERT INTO
articolo_trash (id_ente, id_produttore, id_articolo, venditore_id_ente,
id_prodotto, aggregato_id_ente, aggregato_id_produttore, aggregato_id_articolo,
descr, url, datasheet, scheda_sicurezza, peso, lunghezza, larghezza, altezza,
volume, max_strati, um, barcode, tipo, bigoid, metadata, finalized,
xdbs_created, xdbs_modified) VALUES (old.id_ente, old.id_produttore,
old.id_articolo, old.venditore_id_ente, old.id_prodotto, old.aggregato_id_ente,
old.aggregato_id_produttore, old.aggregato_id_articolo, old.descr, old.url,
old.datasheet, old.scheda_sicurezza, old.peso, old.lunghezza, old.larghezza,
old.altezza, old.volume, old.max_strati, old.um, old.barcode, old.tipo,
old.bigoid, old.metadata, old.finalized, old.xdbs_created, old.xdbs_modified)
Triggers:
     articolo_update_trigger BEFORE UPDATE ON articolo FOR EACH ROW EXECUTE
PROCEDURE xdbs_update_trigger()
Inherits: object,
           barcode


                                      Table "public.ubicazione"
     Column     |            Type             |                      Modifiers
---------------+-----------------------------+-----------------------------------------------------
  bigoid        | bigint                      | not null default
nextval('object_bigoid_seq'::text)
  metadata      | text                        |
  finalized     | timestamp without time zone |
  xdbs_created  | timestamp without time zone | default now()
  xdbs_modified | timestamp without time zone |
  id_ente       | text                        | not null
  barcode       | text                        |
  tipo          | text                        |
  id_magazzino  | text                        | not null
  id_settore    | text                        | not null
  id_area       | text                        | not null
  id_ubicazione | text                        | not null
  flavor        | text                        |
  peso_max      | numeric                     |
  lunghezza     | numeric                     |
  larghezza     | numeric                     |
  altezza       | numeric                     |
  volume_max    | numeric                     |
  inventario    | integer                     | default 0
  allarme       | text                        |
  manutenzione  | text                        |
  id_produttore | text                        |
  id_articolo   | text                        |
  quantita      | numeric                     |
  in_prelievo   | numeric                     |
  in_deposito   | numeric                     |
  lotto         | text                        |
  scadenza      | date                        |
Indexes:
     "ubicazione_pkey" primary key, btree (id_ente, id_magazzino, id_settore,
id_area, id_ubicazione)
     "ubicazione_id_ubicazione_key" unique, btree (id_ubicazione)
     "ubicazione_fkey_articolo" btree (id_ente, id_produttore, id_articolo)
     "ubicazione_modified_index" btree (xdbs_modified)
Foreign-key constraints:
     "$5" FOREIGN KEY (id_ente, id_produttore, id_articolo) REFERENCES
articolo(id_ente, id_produttore, id_articolo) DEFERRABLE INITIALLY DEFERRED
     "$4" FOREIGN KEY (manutenzione) REFERENCES manutenzione(manutenzione)
DEFERRABLE INITIALLY DEFERRED
     "$3" FOREIGN KEY (allarme) REFERENCES allarme(allarme) DEFERRABLE INITIALLY
DEFERRED
     "$2" FOREIGN KEY (flavor) REFERENCES flavor(flavor) DEFERRABLE INITIALLY
DEFERRED
     "$1" FOREIGN KEY (id_ente, id_magazzino, id_settore, id_area) REFERENCES
area(id_ente, id_magazzino, id_settore, id_area) DEFERRABLE INITIALLY DEFERRED
Rules:
     ubicazione_delete_rule AS ON DELETE TO ubicazione DO INSERT INTO
ubicazione_trash (id_ente, id_magazzino, id_settore, id_area, id_ubicazione,
flavor, peso_max, lunghezza, larghezza, altezza, volume_max, inventario,
allarme, manutenzione, id_produttore, id_articolo, quantita, in_prelievo,
in_deposito, lotto, scadenza, barcode, tipo, bigoid, metadata, finalized,
xdbs_created, xdbs_modified) VALUES (old.id_ente, old.id_magazzino,
old.id_settore, old.id_area, old.id_ubicazione, old.flavor, old.peso_max,
old.lunghezza, old.larghezza, old.altezza, old.volume_max, old.inventario,
old.allarme, old.manutenzione, old.id_produttore, old.id_articolo, old.quantita,
old.in_prelievo, old.in_deposito, old.lotto, old.scadenza, old.barcode,
old.tipo, old.bigoid, old.metadata, old.finalized, old.xdbs_created,
old.xdbs_modified)
     ubicazione_update_rule AS ON UPDATE TO ubicazione WHERE
((new.xdbs_modified)::timestamp with time zone <> now()) DO INSERT INTO
ubicazione_trash (id_ente, id_magazzino, id_settore, id_area, id_ubicazione,
flavor, peso_max, lunghezza, larghezza, altezza, volume_max, inventario,
allarme, manutenzione, id_produttore, id_articolo, quantita, in_prelievo,
in_deposito, lotto, scadenza, barcode, tipo, bigoid, metadata, finalized,
xdbs_created, xdbs_modified) VALUES (old.id_ente, old.id_magazzino,
old.id_settore, old.id_area, old.id_ubicazione, old.flavor, old.peso_max,
old.lunghezza, old.larghezza, old.altezza, old.volume_max, old.inventario,
old.allarme, old.manutenzione, old.id_produttore, old.id_articolo, old.quantita,
old.in_prelievo, old.in_deposito, old.lotto, old.scadenza, old.barcode,
old.tipo, old.bigoid, old.metadata, old.finalized, old.xdbs_created,
old.xdbs_modified)
Triggers:
     ubicazione_update_trigger BEFORE UPDATE ON ubicazione FOR EACH ROW EXECUTE
PROCEDURE xdbs_update_trigger()
Inherits: object,
           barcode

******************************************************************************

Here is the first join. This is planned correctly. Execution times are irrelevant.

dmd-freerp-1-alex=# explain analyze SELECT *  FROM articolo JOIN ubicazione
USING  (id_ente, id_produttore, id_articolo) WHERE ubicazione.id_ente =  'dmd'
AND allarme IS NULL AND manutenzione IS NULL AND ubicazione.xdbs_modified >
'2006-01-08 18:25:00+01';
                                                                           QUERY
PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------------------
  Nested Loop  (cost=0.00..8.73 rows=1 width=1146) (actual time=0.247..0.247
rows=0 loops=1)
    ->  Index Scan using ubicazione_modified_index on ubicazione
(cost=0.00..3.03 rows=1 width=536) (actual time=0.239..0.239 rows=0 loops=1)
          Index Cond: (xdbs_modified > '2006-01-08 18:25:00'::timestamp without
time zone)
          Filter: ((id_ente = 'dmd'::text) AND (allarme IS NULL) AND
(manutenzione IS NULL))
    ->  Index Scan using articolo_pkey on articolo  (cost=0.00..5.69 rows=1
width=653) (never executed)
          Index Cond: (('dmd'::text = articolo.id_ente) AND
(articolo.id_produttore = "outer".id_produttore) AND (articolo.id_articolo =
"outer".id_articolo))
  Total runtime: 0.556 ms
(7 rows)

*********************************************************************

Here's the second join on the same tables. This times a different set of indexes
should be used to perform the join, but even in this case I would expect the
planner to generate a nested loop of two index scans. Instead, this is what happens.


dmd-freerp-1-alex=# explain analyze SELECT *  FROM articolo JOIN ubicazione
USING  (id_ente, id_produttore, id_articolo) WHERE ubicazione.id_ente =  'dmd'
AND allarme IS NULL AND manutenzione IS NULL AND articolo.xdbs_modified >
'2006-01-08 18:25:00+01';
                                                                           QUERY
PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------------------
  Nested Loop  (cost=0.00..1017.15 rows=1 width=1146) (actual
time=258.648..258.648 rows=0 loops=1)
    ->  Seq Scan on ubicazione  (cost=0.00..1011.45 rows=1 width=536) (actual
time=0.065..51.617 rows=12036 loops=1)
          Filter: ((id_ente = 'dmd'::text) AND (allarme IS NULL) AND
(manutenzione IS NULL))
    ->  Index Scan using articolo_pkey on articolo  (cost=0.00..5.69 rows=1
width=653) (actual time=0.011..0.011 rows=0 loops=12036)
          Index Cond: (('dmd'::text = articolo.id_ente) AND
(articolo.id_produttore = "outer".id_produttore) AND (articolo.id_articolo =
"outer".id_articolo))
          Filter: (xdbs_modified > '2006-01-08 18:25:00'::timestamp without time
zone)
  Total runtime: 258.975 ms
(7 rows)

This time, a sequential scan on the rightmost table is used to perform the join.
This is quite plainly a wrong choice, since the number of tuples in the articolo
having xdbs_modified > '2006-01-08 18:25:00' is 0. I also tried increasing the
amount of collected statistics to 1000 with "ALTER TABLE articolo ALTER COLUMN
xdbs_modified SET STATISTICS 1000" and subsequently vacuum-analyzed the db, so
as to give the planner as much information as possible to realize that articolo
ought to be index-scanned with the articolo_modified_index B-tree index. The
correct query plan is to perform a nested loop join with an index scan on
articolo using xdbs_modified_index and a corresponding index scan on ubicazione
using ubicazione_fkey_articolo.

I am currently resorting to selecting from the single tables and performing the
join in the application code rather than in the DB. This is currently the only
viable alternative for me, as a 500x speed-down simply cannot be tolerated.

What I do not understand is why the planner behaves so differently in the two
cases. Any ideas? Would upgrading to more recent versions of postgresql make any
difference?



Alex



--
*********************************************************************
http://www.barettadeit.com/
Baretta DE&IT
A division of Baretta SRL

tel. +39 02 370 111 55
fax. +39 02 370 111 54

Our technology:

The Application System/Xcaml (AS/Xcaml)
<http://www.asxcaml.org/>

The FreerP Project
<http://www.freerp.org/>