Skip to main content

Posts

Showing posts from December, 2011

Seq scan vs Index Scan

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;
QUERY PLAN
----------------------…

:: Rownum in postgresql ::

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 T,NEXTVAL('ROWNUMSEQ') AS ROWNUM FROM TES…