Re: [BUGS] [GENERAL] Partial indices...

#1

Martijn van Oosterhout <kleptog (AT) svana (DOT) org> writes:

Quote:

That's very strange.

Not really. In the dummy example, the two indexes are exactly the same
size and so there is no reason for the planner to prefer one over the
other. (Note that the two plans have exactly the same estimated cost.)

In a real example, with appropriate statistics from VACUUM or ANALYZE,
the planner would tend to prefer the smaller index.

If we had code to suppress the extra evaluation of the partial-index
condition (which we do not) then possibly the planner would be able
to favor the partial index on the grounds of less computation per row.
I don't see any simple way to suppress those clauses early enough to
let that cost difference figure into planning, though.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

#2

--M9NhX3UHpAaciwkO
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

That's very strange. However, since the tables in question are empty you
could get strange results like this. How many rows does your real app have.
Can you show the explain for each index on your real app?

Hope this helps,

On Fri, Oct 10, 2003 at 03:26:13PM -0400, Dmitry Tkach wrote:

Quote:

Hi, everybody!
=20
I am getting some weird behaviour trying to use a partial index in 7.3:
=20
testdb=3D# create table a (x int, y int, z int);
CREATE
testdb=3D# create index a_idx on a(x,y) where z is null;
CREATE
testdb=3D# create index b_idx on a (x,y);
CREATE
testdb=3D# explain select * from a where x=3D1 and y=3D2 and z is null;
QUERY PLAN=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20
----------------------------------------------------------------
Index Scan using b_idx on a (cost=3D0.00..4.83 rows=3D1 width=3D12)
Index Cond: ((x =3D 1) AND (y =3D 2))
Filter: (z IS NULL)
(3 rows)
=20
=20
Any idea, why is it using b_idx with a filter, instead of going straight=
=20
for a_idx?
Another thing is, if I drop b_idx, it then starts using a_idx, but=20
*still* has that 'Filter:' thing in the query plan...
I understand, that the latter doesn't hurt much... but the former=20
*does*, because in my "real life" app, (much) less then half of entries=
=20
are non-null.... :-(
=20
Thanks!
=20
Dima
=20
=20
=20
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

--=20
Martijn van Oosterhout <kleptog (AT) svana (DOT) org> http://svana.org/kleptog/

Quote:

"All that is needed for the forces of evil to triumph is for enough good
men to do nothing." - Edmond Burke
"The penalty good people pay for not being interested in politics is to be
governed by people worse than themselves." - Plato

--M9NhX3UHpAaciwkO
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE/h9wnY5Twig3Ge+YRAkFNAJ4shkkDfvyTAN5iQ/BDmz6olj/QkACfWIbR
z6BegFP3Kshr6+TnK2dMZdY=
=FNdi
-----END PGP SIGNATURE-----

--M9NhX3UHpAaciwkO--

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: [BUGS] [GENERAL] Partial indices...

mailing.database.pgsql-bugs mailing.database.pgsql-bugs

Re: [BUGS] [GENERAL] Partial indices... - 10-11-2003 , 12:14 PM

Re: [BUGS] [GENERAL] Partial indices... - 10-14-2003 , 11:12 AM