Date vs Timestamp without timezone Partition Key

[Cedric Leong]

Just in case someone is interested enough to answer this. Does anyone know
if the performance for a date column vs a timestamp column as a partition
key is large? What i mean with large is that say you have 6 partitions with
10GB each. Would it be a 10 second+ difference? An explanation of how this
internally works would be appreciated.

Re: Date vs Timestamp without timezone Partition Key

[Tom Lane]

Cedric Leong  writes:
> Just in case someone is interested enough to answer this. Does anyone know
> if the performance for a date column vs a timestamp column as a partition
> key is large?

I doubt it's even measurable, at least on 64-bit machines.  You're
basically talking about 32-bit integer comparisons vs 64-bit integer
comparisons.

On a 32-bit machine it's possible that an index on a date column
will be physically smaller, so you could get some wins from reduced
I/O.  But on (most?) 64-bit machines that difference goes away too,
because of alignment restrictions.

As always, YMMV; it never hurts to do your own testing.

regards, tom lane

Re: Request to help on Query improvement suggestion.

[Laurenz Albe]

On Fri, 2020-05-22 at 16:15 +0530, devchef2020 d wrote:
> PostgreSQL : 9.5.15

> Created Indexes on column parental_path:
> =
> 
> CREATE INDEX cable_pair_parental_path_idx
>   ON SCHEMA.TABLE_NAME
>   USING btree
>   (md5(parental_path) COLLATE pg_catalog."default");
>   
> CREATE INDEX cable_pair_parental_path_idx_fulltext
>   ON SCHEMA.TABLE_NAME
>   USING gist
>   (parental_path COLLATE pg_catalog."default");

> SELECT seq_no + 1 FROM SCHEMA.TABLE_NAME WHERE (parental_path LIKE 
> '%,sheath--' || cable_seq_id || ',%' OR parental_path LIKE 'sheath--' || 
> cable_seq_id || ',%' OR parental_path LIKE '%,sheath--' ||
> cable_seq_id OR parental_path = 'sheath--' || cable_seq_id) ORDER BY seq_no 
> DESC LIMIT 1;
> 
> Explain Plan:
> =
> 
> Limit  (cost=108111.60..108111.61 rows=1 width=4) (actual 
> time=4597.605..4597.605 rows=0 loops=1)
>  Output: ((seq_no + 1)), seq_no
>  Buffers: shared hit=2967 read=69606 dirtied=1
>  ->  Sort  (cost=108111.60..108113.09 rows=595 width=4) (actual 
> time=4597.603..4597.603 rows=0 loops=1)
>Output: ((seq_no + 1)), seq_no
>Sort Key: TABLE_NAME.seq_no DESC
>Sort Method: quicksort  Memory: 25kB
>Buffers: shared hit=2967 read=69606 dirtied=1
>->  Seq Scan on SCHEMA.TABLE_NAME  (cost=0.00..108108.63 rows=595 
> width=4) (actual time=4597.595..4597.595 rows=0 loops=1)
>  Output: (seq_no + 1), seq_no
>  Filter: ((TABLE_NAME.parental_path ~~ '%,sheath--64690,%'::text) 
> OR (TABLE_NAME.parental_path ~~ 'sheath--64690,%'::text) OR 
> (TABLE_NAME.parental_path ~~ '%,sheath--64690'::text) OR
> (TABLE_NAME.parental_path = 'sheath--64690'::text))
>  Rows Removed by Filter: 1930188
>  Buffers: shared hit=2967 read=69606 dirtied=1

An index on an expression can only be used if the expression is exactly the 
same as on one
side of an operator in a WHERE condition.

So your only chance with that query is to hope for a bitmap OR with an index on 
"parental path".

Two things to try:

1)  CREATE INDEX ON table_name (parental_path text_pattern_ops);

2)  CREATE EXTENSION pg_trgm;
CREATE INDEX ON table_name USING GIN (parental_path gin_trgm_ops);

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com