Serial Number and Indexing in Postgre SQL University

  • Slides: 17
Download presentation
Serial Number and Indexing in Postgre. SQL University of Tehran ECE Department Database Lab.

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

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 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

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

Indexing in Postgre. SQL

CREATE TABLE test 1 ( id integer, content varchar ); SELECT content FROM test

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.

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

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

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

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

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

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

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 Spatial Database

Post. GIS Example CREATE TABLE "public". "GASSTATION"("gid" SERIAL PRIMARY KEY, "gs_id" float, "name" varchar(48),

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"

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

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))