From: Laszlo Nagy <gandalf@shopzeus.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Cc: Daniel Fekete <danieleff@gmail.com>
Subject: Bad query plan when the wrong data type is used
Date: Tue, 08 Feb 2011 15:15:27 +0100
Message-ID: <4D514FFF.9060308@shopzeus.com> (raw)
This query:
select p.id,p.producer_id,visa.variation_item_id, vi.qtyavail
from variation_item_sellingsite_asin visa
inner join product p on p.id = visa.product_id
inner join variation_item vi on vi.id =
visa.variation_item_id
where visa.id =4
runs in 43 msec. The "visa.id" column has int4 datatype. The query plan
uses an index condition:
"Nested Loop (cost=0.00..26.19 rows=1 width=28)"
" -> Nested Loop (cost=0.00..17.75 rows=1 width=24)"
" -> Index Scan using variation_item_sellingsite_asin_pkey on
variation_item_sellingsite_asin visa (cost=0.00..8.58 rows=1 width=16)"
" Index Cond: (id = 4)"
" -> Index Scan using pk_product_id on product p
(cost=0.00..9.16 rows=1 width=16)"
" Index Cond: (p.id = visa.product_id)"
" -> Index Scan using pk_variation_item_id on variation_item vi
(cost=0.00..8.43 rows=1 width=12)"
" Index Cond: (vi.id = visa.variation_item_id)"
This query:
select p.id,p.producer_id,visa.variation_item_id, vi.qtyavail
from variation_item_sellingsite_asin visa
inner join product p on p.id = visa.product_id
inner join variation_item vi on vi.id =
visa.variation_item_id
where visa.id =4.0
Runs for 1144 msec! Query plan uses seq scan + filter:
"Nested Loop (cost=33957.27..226162.68 rows=14374 width=28)"
" -> Hash Join (cost=33957.27..106190.76 rows=14374 width=20)"
" Hash Cond: (visa.variation_item_id = vi.id)"
" -> Seq Scan on variation_item_sellingsite_asin visa
(cost=0.00..71928.04 rows=14374 width=16)"
" Filter: ((id)::numeric = 4.0)"
" -> Hash (cost=22026.01..22026.01 rows=954501 width=12)"
" -> Seq Scan on variation_item vi (cost=0.00..22026.01
rows=954501 width=12)"
" -> Index Scan using pk_product_id on product p (cost=0.00..8.33
rows=1 width=16)"
" Index Cond: (p.id = visa.product_id)"
Which is silly. I think that PostgreSQL converts the int side to a
float, and then compares them.
It would be better to do this, for each item in the loop:
* evaluate the right side (which is float)
* tell if it is an integer or not
* if not an integer, then discard the row immediately
* otherwise use its integer value for the index scan
The result is identical, but it makes possible to use the index scan. Of
course, I know that the query itself is wrong, because I sould not use a
float where an int is expected. But this CAN be optimized, so I think it
should be! My idea for the query optimizer is not to use the "wider"
data type, but use the data type that has an index on it instead.
(I spent an hour figuring out what is wrong with my program. In some
cases it was slow, in other cases it was really fast, and I never got an
error message.)
What do you think?
Laszlo
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gandalf@shopzeus.com, danieleff@gmail.com
Subject: Re: Bad query plan when the wrong data type is used
In-Reply-To: <4D514FFF.9060308@shopzeus.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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