From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Valentine Gogichashvili" <valgog(at)gmail(dot)com> |
Cc: | "Oleg Bartunov" <oleg(at)sai(dot)msu(dot)su>, pgsql-performance(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: [PERFORM] Cannot make GIN intarray index be used by the planner |
Date: | 2007-05-09 19:18:12 |
Message-ID: | 29856.1178738292@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-performance |
[cc'ing to pgsql-hackers since this is looking like a contrib/intarray bug]
"Valentine Gogichashvili" <valgog(at)gmail(dot)com> writes:
> here is the DT
That works fine for me in 8.2:
regression=# explain SELECT id, (myintarray_int4)
FROM myintarray_table_nonulls
WHERE ARRAY[8] <@ myintarray_int4;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Index Scan using idx_nonnulls_myintarray_int4_gin on myintarray_table_nonulls (cost=0.00..8.27 rows=1 width=36)
Index Cond: ('{8}'::integer[] <@ myintarray_int4)
(2 rows)
What I am betting is that you've installed contrib/intarray in this
database and that's bollixed things up somehow. In particular, intarray
tries to take over the position of "default" gin opclass for int4[],
and the opclass that it installs as default has operators named just
like the built-in ones. If somehow your query is using pg_catalog.<@
instead of intarray's public.<@, then the planner wouldn't think the
index is relevant.
In a quick test your example still works with intarray installed, because
what it's really created is public.<@ (integer[], integer[]) which is
an exact match and therefore takes precedence over the built-in
pg_catalog.<@ (anyarray, anyarray). But if for example you don't have
public in your search_path then the wrong operator would be chosen.
Please look at the pg_index entry for your index, eg
select * from pg_index where indexrelid =
'"versionA".idx_nonnulls_myintarray_int4_gin'::regclass;
and see whether the index opclass is the built-in one or not.
Note to hackers: we've already discussed that intarray shouldn't be
trying to take over the default gin opclass, but I am beginning to
wonder if it still has a reason to live at all. We should at least
consider removing the redundant operators to avoid risks like this one.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Oleg Bartunov | 2007-05-09 19:20:27 | Re: Problem with "create database ... with template " |
Previous Message | Andrew Dunstan | 2007-05-09 19:04:05 | Re: Problem with "create database ... with template " |
From | Date | Subject | |
---|---|---|---|
Next Message | Y Sidhu | 2007-05-09 22:00:22 | Vacuum Times - Verbose and maintenance_work_mem |
Previous Message | david | 2007-05-09 17:55:44 | Re: ZFS and Postgresql - WASRe: Best OS for Postgres 8.2 |