Re: Issue in compiling postgres on latest macOS 14.1.1

[Shaik Mohammad Mujeeb]

Hi Team,

As said by Tom Lane, I had some outdated binaries in my PATH. After
removing those outdated binaries from the PATH, I didn't face any errors in
postgres compilation.

Thank you so much for all your assistance.

Regards,
Mujeeb.

On Tue, Nov 14, 2023 at 1:29 AM Alban Hertroys  wrote:

>
> > On 13 Nov 2023, at 18:00, Tom Lane  wrote:
>
> (…)
>
> > * If you use MacPorts or Homebrew, maybe that's out of date?
> > Try removing the associated directories from your PATH to see
> > if it works better.
>
> Perhaps even worse; you had old binaries from an Intel architecture that
> were migrated onto a new ARM-based architecture? In that case the Homebrew
> uninstall scripts won’t even work anymore - at least not w/o Rosetta 2 - as
> they’re Intel-based too.
>
> A migration assistant can also work too well, I found.
>
> Alban Hertroys
> --
> There is always an exception to always.
>
>
>
>
>

Re: Indexing fragments of a column's value ?

[dld]

You can index on expressions, and these will be recognised by the query 
generator.


drop schema tmp CASCADE;
create schema tmp;
set search_path = tmp;

CREATE TABLE bagger
    ( eight CHAR(8) NOT NULL PRIMARY KEY
    , more text
    );
CREATE INDEX bagger_idx_12 ON bagger (substr(eight,1,2));
CREATE INDEX bagger_idx_34 ON bagger (substr(eight,3,2));
CREATE INDEX bagger_idx_58 ON bagger (substr(eight,5,4));

INSERT INTO bagger(eight, more)
SELECT translate(to_hex( gs), ' ' , '0')
    , gs::text
FROM generate_series(0,40, 64999) gs
    ;

VACUUM ANALYZE bagger;

EXPLAIN ANALYZE
SELECT * FROM bagger
WHERE 1=1
    -- AND eight >= '00' AND eight < '05'
    AND substr(eight, 1,2) >= '30' AND substr(eight,1,2) < '05'
    AND substr(eight, 3,2) >= '90' AND substr(eight,3,2) < 'A5'
    AND substr(eight, 5,4) >= '' AND substr(eight,5,4) < ''
    ;

/*** The optimiser is smart enough to ignore one of the indexes. */
QUERY PLAN
-
 Bitmap Heap Scan on bagger  (cost=41.46..59.62 rows=1 width=19) 
(actual time=0.049..0.056 rows=0 loops=1)
   Recheck Cond: ((substr((eight)::text, 1, 2) >= '30'::text) AND 
(substr((eight)::text, 1, 2) < '05'::text) AND (substr((eight)::text, 3, 
2) >= '90'::text) AND (substr((eight)::text, 3, 2) < 'A5'::text))
   Filter: ((substr((eight)::text, 5, 4) >= ''::text) AND 
(substr((eight)::text, 5, 4) < ''::text))
   ->  BitmapAnd  (cost=41.46..41.46 rows=16 width=0) (actual 
time=0.042..0.047 rows=0 loops=1)
 ->  Bitmap Index Scan on bagger_idx_12 (cost=0.00..4.47 
rows=308 width=0) (actual time=0.039..0.039 rows=0 loops=1)
   Index Cond: ((substr((eight)::text, 1, 2) >= '30'::text) 
AND (substr((eight)::text, 1, 2) < '05'::text))
 ->  Bitmap Index Scan on bagger_idx_34 (cost=0.00..36.74 
rows=3205 width=0) (never executed)
   Index Cond: ((substr((eight)::text, 3, 2) >= '90'::text) 
AND (substr((eight)::text, 3, 2) < 'A5'::text))

 Planning Time: 5.487 ms
 Execution Time: 0.310 ms
(10 rows)


HTH,

AvK