DevAdmin

Hi, Today, let me discuss some thing about PG Optimizer while taking a better execution plan. Seq Scan (vs) Index Scan ---------------------------------- Both scans having its own pros and cons. Sometimes, seq scan will give you the better execution time and sometimes Index scan. Let's find out.. Step 1:- postgres=# CREATE TABLE TEST(T INT PRIMARY KEY); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test" CREATE TABLE postgres=# INSERT INTO TEST VALUES(GENERATE_SERIES(1,100)); INSERT 0 100 Step 2:- Check the no.of pages occupied by the table "test" from pg_class. postgres=# ANALYZE TEST; ANALYZE postgres=# SELECT RELNAME,RELPAGES FROM PG_CLASS WHERE relname like 'test'; relname | relpages ---------+---------- test | 1 Step 3:- Find the below execution plans. postgres=# EXPLAIN ANALYZE SELECT * FROM TEST WHERE T=10; ...

Hi All, As we know,we can generate rownum with window fuctions also(Rank).In oracle we have "rownum" pseduo column,but we don't in postgresql. Here is the one of the solution for generating rownum in postgresql. In Oracle ----------- SQL> CREATE SEQUENCE ROWNUMSEQ; Sequence created. SQL> SELECT EMPNO,ROWNUMSEQ.NEXTVAL AS "ROWNUM" FROM EMP; EMPNO ROWNUM ---------- ---------- 7369 1 7499 2 7521 3 7566 4 7654 5 7698 6 7782 7 7788 8 7839 9 7844 10 7876 11 7900 12 7902 13 7934 14 In PostgreSQL ---------------- postgres=# CREATE SEQUENCE ROWNUMSEQ; CREATE SEQUENCE postgres=# CREATE TABLE TEST(T INT); CREATE TABLE postgres=# INSERT INTO TEST VALUES(GENERATE_SERIES(1,14)); INSERT 0 14 postgres=# SELECT ...