pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Index not recognized 3+ messages / 3 participants [nested] [flat]
* Index not recognized @ 2003-12-06 05:05 Grace C. Unson <gracec@ntsp.nec.co.jp> 2003-12-07 00:36 ` Re: Index not recognized Greg Stark <gsstark@mit.edu> 0 siblings, 1 reply; 3+ messages in thread From: Grace C. Unson @ 2003-12-06 05:05 UTC (permalink / raw) To: pgsql-sql Hello. Why is it that my index for text[] data type is not recognized by the Planner? I did these steps: 1. create function textarr(text[]) returns text language sql as 'select $1[1]' strict immutable 2. create index org_idx on EmpData (textarr(org)); 3. vacuum full 4. explain analyze select name from EmpData where org *= 'math'; Result: ========= Seq Scan on EmpData (cost=0.00..3193.20 rows=102 width=488) (actual time=3.71.35..371.35 rows=0 loops=1) Filter: (org[0]='math'::text) Total runtime: 371.47 msec (3 rows) ========== I have 20,496 records. My postgres version is 7.3. Any help is greatly appreciated. Thank you.. Ace ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Index not recognized 2003-12-06 05:05 Index not recognized Grace C. Unson <gracec@ntsp.nec.co.jp> @ 2003-12-07 00:36 ` Greg Stark <gsstark@mit.edu> 2003-12-09 08:37 ` Re: Index not recognized Ace <gracec@ntsp.nec.co.jp> 0 siblings, 1 reply; 3+ messages in thread From: Greg Stark @ 2003-12-07 00:36 UTC (permalink / raw) To: Grace C. Unson <gracec@ntsp.nec.co.jp>; +Cc: pgsql-sql "Grace C. Unson" <gracec@ntsp.nec.co.jp> writes: > Why is it that my index for text[] data type is not recognized by the > Planner? > > I did these steps: > > 1. create function textarr(text[]) returns text language sql as 'select > $1[1]' strict immutable > 2. create index org_idx on EmpData (textarr(org)); This index will only be used if you use the expression textarr(org) in your query. You would probably have some success if you did: select * from empdata where textarr(org) = 'math' > 3. vacuum full > 4. explain analyze select name from EmpData where org *= 'math'; Is this *= operator from the contrib/array directory? It's not an indexable operator at all using standard btree indexes. The GiST indexing does make indexable operators that can do things like *= but that's a whole other ball of wax. What are you really trying to do? > Result: > ========= > Seq Scan on EmpData (cost=0.00..3193.20 rows=102 width=488) > (actual time=3.71.35..371.35 rows=0 loops=1) > > Filter: (org[0]='math'::text) Well that's awfully odd. I don't know how that expression came out of the query you gave. You'll have to give a lot more information about how you're defining *= and why you think it's related to the function you used to define the index. -- greg ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Index not recognized 2003-12-06 05:05 Index not recognized Grace C. Unson <gracec@ntsp.nec.co.jp> 2003-12-07 00:36 ` Re: Index not recognized Greg Stark <gsstark@mit.edu> @ 2003-12-09 08:37 ` Ace <gracec@ntsp.nec.co.jp> 0 siblings, 0 replies; 3+ messages in thread From: Ace @ 2003-12-09 08:37 UTC (permalink / raw) To: Greg Stark <gsstark@mit.edu>; +Cc: pgsql-sql Hello Greg, thanks for replying. I definitely lacked the expression you suggested in my 'SELECT' statement. > Is this *= operator from the contrib/array directory? It's not an indexable > operator at all using standard btree indexes. Yes, it is from contrib/array directory. > The GiST indexing does make indexable operators that can do things like *= but > that's a whole other ball of wax. I tried the btree_gist from contrib/, but I know I missed something because I got this error message: data type text[] has no default operator class for access method "gist". You must specify an operator class for the index or define a default operator class for the data type. > What are you really trying to do? I have tables with attributes whose datatype is TEXT[]. I'm interested to find out the time it will take to finish an array search with and without an index. --- Grace ----- Original Message ----- From: "Greg Stark" <gsstark@mit.edu> To: "Grace C. Unson" <gracec@ntsp.nec.co.jp> Cc: "PgSQL SQL" <pgsql-sql@postgresql.org> Sent: Sunday, December 07, 2003 8:36 AM Subject: Re: [SQL] Index not recognized > > "Grace C. Unson" <gracec@ntsp.nec.co.jp> writes: > > > Why is it that my index for text[] data type is not recognized by the > > Planner? > > > > I did these steps: > > > > 1. create function textarr(text[]) returns text language sql as 'select > > $1[1]' strict immutable > > 2. create index org_idx on EmpData (textarr(org)); > > This index will only be used if you use the expression textarr(org) in your > query. You would probably have some success if you did: > > select * from empdata where textarr(org) = 'math' > > > 3. vacuum full > > 4. explain analyze select name from EmpData where org *= 'math'; > > Is this *= operator from the contrib/array directory? It's not an indexable > operator at all using standard btree indexes. > > The GiST indexing does make indexable operators that can do things like *= but > that's a whole other ball of wax. > > What are you really trying to do? > > > Result: > > ========= > > Seq Scan on EmpData (cost=0.00..3193.20 rows=102 width=488) > > (actual time=3.71.35..371.35 rows=0 loops=1) > > > > Filter: (org[0]='math'::text) > > Well that's awfully odd. I don't know how that expression came out of the > query you gave. You'll have to give a lot more information about how you're > defining *= and why you think it's related to the function you used to define > the index. > > -- > greg > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-12-09 08:37 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2003-12-06 05:05 Index not recognized Grace C. Unson <gracec@ntsp.nec.co.jp> 2003-12-07 00:36 ` Greg Stark <gsstark@mit.edu> 2003-12-09 08:37 ` Ace <gracec@ntsp.nec.co.jp>
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