Serial Number and Indexing in Postgre SQL University

Serial Number and Indexing in Postgre. SQL University of Tehran ECE Department Database Lab.

Serial Number Definition CREATE TABLE Test ( id SERIAL PRIMARY KEY, name character varying (20) ) INSERT INTO Test ("name") values ('Ali'); Incorrect: INSERT INTO Test values ('Ali');

Serial Data Types Data Type Storage Size Range smallserial 2 bytes 1 to 32767 serial 4 bytes 1 to 2147483647 bigserial 8 bytes 1 to 9223372036854775807

Another method for Serial Number Definition CREATE SEQUENCE Test_id_seq; CREATE TABLE tablename ( id integer NOT NULL DEFAULT nextval(' Test_id_seq ') ); ALTER SEQUENCE Test_id_seq OWNED BY Test. id;

Indexing in Postgre. SQL

CREATE TABLE test 1 ( id integer, content varchar ); SELECT content FROM test 1 WHERE id = 10; CREATE INDEX test 1_id_index ON test 1 (id);

Index Types Postgre. SQL provides several index types: ◦ ◦ ◦ B-tree Hash Gi. ST SP-Gi. ST GIN Each index type uses a different algorithm that is best suited to different types of queries. ◦ By default, the CREATE INDEX command creates Btree indexes, which fit the most common situations.

B+Tree Usage equality and range queries ◦ ◦ < <= = >= > BETWEEN and IN IS NULL or IS NOT NULL pattern matching operators LIKE and ~ if the pattern is a constant and is anchored to the beginning of the string for example, col LIKE 'foo%' or col ~ '^foo', but not col LIKE '%bar'. ◦ can also be used to retrieve data in sorted order. This is not always faster than a simple scan and sort, but it is often helpful.

Hash Usage CREATE INDEX name ON table USING hash (column); Hash indexes can only handle simple equality comparisons. ◦ = operator.

Gi. ST Usage not a single kind of index, but rather an infrastructure within which many different indexing strategies can be implemented. the particular operators with which a Gi. ST Postgre. SQL includes Gi. ST operator classes for several two-dimensional geometric data types, which support indexed queries using these operators: ◦ << @> &< <@ &> ~= >> && <<| &<| |&> |>>

Example Gi. ST indexes are also capable of optimizing "nearest-neighbor" searches, such as SELECT * FROM places ORDER BY location <> point '(101, 456)' LIMIT 10; which finds the ten places closest to a given target point. The ability to do this is again dependent on the particular operator class being used.

SP-Gi. ST like Gi. ST indexes offer an infrastructure that supports various kinds of searches. SP-Gi. ST permits implementation of a wide range of different non-balanced disk-based data structures, such as quadtrees, k-d trees, and radix trees (tries). Postgre. SQL includes SP-Gi. ST operator classes for two-dimensional points: ◦ << >> ~= <@ <^ >^

GIN indexes are inverted indexes which can handle values that contain more than one key, arrays for example. Like Gi. ST and SP-Gi. ST, GIN can support many different user-defined indexing strategies and the particular operators classes for onedimensional arrays ◦ <@ @> = &&

Post. GIS Spatial Database

Post. GIS Example CREATE TABLE "public". "GASSTATION"("gid" SERIAL PRIMARY KEY, "gs_id" float, "name" varchar(48), "type" varchar(16) ) SELECT Add. Geometry. Column('public', 'GASSTATION', 'geom', 4326, 'POI NT', 2) INSERT INTO "public". "GASSTATION" ( "gs_id", "name", "type", "geom") VALUES ('3. 21805 e+07', 'Amir. Abaad', 'GAS', st_geometryfromtext('POINT (53. 72 39. 04)', 4326))

Post. GIS Example SELECT "name", st_distance("geom", st_geometryfromtext('POINT(51. 72 38. 04)', 4326)) FROM "public". "GASSTATION" SELECT * FROM "public". "GASSTATION" WHERE st_distance("geom", st_geometryfromtext('POINT(51. 72 38. 04)', 4326)) < 10

Create Gi. ST index in Post. GIS CREATE INDEX gs_index ON "public". "GASSTATION" USING GIST (geom); SELECT * FROM "public". "GASSTATION" WHERE _ST_Contains(geom, st_geometryfromtext('POLYGON((0 0, 0 10, 10 0, 0 0))', 4326))