LedaTips

Leda Tips

• Get LIKE make use index for non-C locale

cas=# create index design_idx on leda.designation (lower(design) varchar_pattern_ops);
cas=# vacuum analyze leda.designation;
cas=# explain select * from leda.designation l,  
(select pgc from leda.designation 
where lower(design) like 'ngc0224') as foo 
where l.pgc = foo.pgc;
                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..53.28 rows=31 width=32)
   ->  Index Scan using design_idx on designation  (cost=0.00..6.02 rows=1 width=4)
         Index Cond: (lower((design)::text) ~=~ 'ngc0224'::character varying)
         Filter: (lower((design)::text) ~~ 'ngc0224'::text)
   ->  Index Scan using pgcnamidx on designation l  (cost=0.00..47.11 rows=12 width=28)
         Index Cond: (l.pgc = "outer".pgc)
(6 rows)

• Find all names ( use egret to get canonical spelling )

cas=# select * from leda.designation l,  
(select pgc from leda.designation 
where lower(design) like 'ngc0224') as foo 
where l.pgc = foo.pgc;
 pgc  |         design         | flag | nc | pgc
------+------------------------+------+----+------
 2557 | NGC0224                |    0 |  1 | 2557
 2557 | UGC00454               |    0 |  3 | 2557
 2557 | MCG+07-02-016          |    0 |  5 | 2557
 2557 | MESSIER031             |    0 |  9 | 2557
 2557 | CGCG535-017            |    0 | 10 | 2557
 2557 | PGC002557              |    0 | 88 | 2557
 2557 | GIN801                 |    0 | 66 | 2557
 2557 | LGG011:[G93]001        |    0 | 84 | 2557
 2557 | 2MASXJ00424433+4116074 |    0 | 78 | 2557
(9 rows)