Views in PgAccess

Started by Fernando Schapachnikover 24 years ago9 messagesgeneral

fschapachnik@vianetworks.com.ar

over 24 years ago

Hi,
Any idea why Views do not show up in PgAccess Views tab
(Postgres 7.1.2, PgAccess 0.98.7)? Any idea on how to solve it?

Thanks!

Fernando P. Schapachnik
Planificaciï¿½n de red y tecnologï¿½a
VIA NET.WORKS ARGENTINA S.A.
fschapachnik@vianetworks.com.ar
Tel.: (54-11) 4323-3381

Denis Gasparin

denis@edinet.it

over 24 years ago

In reply to: Fernando Schapachnik (#1)

No title

Hi to all!
I have created a table using the CREATE TABLE new_table (col1,col2,col3)
AS SELECT col1,col2,col3 FROM org_table.
I create an index on this table using the statement:
CREATE UNIQUE INDEX table_idx ON new_table (col1).
Then i do a select as this:
SELECT * FROM new_table WHERE col1 = 'value'.

The problem is that when i do an explain this is the query plan:

Seq Scan on new_table (cost=0.00..1116.38 rows=500 width=44)

Can anyone explain me why it doesn't use the index I have created?

Thank you for you help...

Bye, Denis

Tom Lane

tgl@sss.pgh.pa.us

over 24 years ago

In reply to: Fernando Schapachnik (#1)

Re: Views in PgAccess

Fernando Schapachnik <fschapachnik@vianetworks.com.ar> writes:

Any idea why Views do not show up in PgAccess Views tab
(Postgres 7.1.2, PgAccess 0.98.7)? Any idea on how to solve it?

Are you *sure* you are using the up-to-date pgaccess? I'd expect
that misbehavior from a pre-7.1 pgaccess.

regards, tom lane

Fernando Schapachnik

fschapachnik@vianetworks.com.ar

over 24 years ago

In reply to: Tom Lane (#3)

Re: Views in PgAccess

En un mensaje anterior, Tom Lane escribiï¿½:

Fernando Schapachnik <fschapachnik@vianetworks.com.ar> writes:

Any idea why Views do not show up in PgAccess Views tab
(Postgres 7.1.2, PgAccess 0.98.7)? Any idea on how to solve it?

Are you *sure* you are using the up-to-date pgaccess? I'd expect
that misbehavior from a pre-7.1 pgaccess.

I confirmed the versions of both PgAccess and Postgres.

Regards.

Fernando P. Schapachnik
Planificaciï¿½n de red y tecnologï¿½a
VIA NET.WORKS ARGENTINA S.A.
fschapachnik@vianetworks.com.ar
Tel.: (54-11) 4323-3381

Doug McNaught

doug@wireboard.com

over 24 years ago

In reply to: Denis Gasparin (#2)

Re:

Denis Gasparin <denis@edinet.it> writes:

Hi to all!
I have created a table using the CREATE TABLE new_table
(col1,col2,col3) AS SELECT col1,col2,col3 FROM org_table.

I create an index on this table using the statement:
CREATE UNIQUE INDEX table_idx ON new_table (col1).
Then i do a select as this:
SELECT * FROM new_table WHERE col1 = 'value'.

The problem is that when i do an explain this is the query plan:

Seq Scan on new_table (cost=0.00..1116.38 rows=500 width=44)>

Can anyone explain me why it doesn't use the index I have created?

How populated is the table? If it's small, or if you haven't done
VACUUM ANALYZE, the statistics may end up preferring a sequential
scan.

-Doug
--
Free Dmitry Sklyarov!
http://www.freesklyarov.org/

We will return to our regularly scheduled signature shortly.

Import Notes

Reply to msg id not found: DenisGasparin'smessageofThu23Aug2001180535+0200

Denis Gasparin

denis@edinet.it

over 24 years ago

In reply to: Doug McNaught (#5)

Re:

It contains 50000 records. I have to do vacuum analyze on the table after
having issued the "CREATE INDEX" to create the index?

Please, let me know...

Regards,
Denis

At 19.03 23/08/01, Doug McNaught wrote:

Show quoted text

Denis Gasparin <denis@edinet.it> writes:

Hi to all!
I have created a table using the CREATE TABLE new_table
(col1,col2,col3) AS SELECT col1,col2,col3 FROM org_table.

I create an index on this table using the statement:
CREATE UNIQUE INDEX table_idx ON new_table (col1).
Then i do a select as this:
SELECT * FROM new_table WHERE col1 = 'value'.

The problem is that when i do an explain this is the query plan:

Seq Scan on new_table (cost=0.00..1116.38 rows=500 width=44)>

Can anyone explain me why it doesn't use the index I have created?

How populated is the table? If it's small, or if you haven't done
VACUUM ANALYZE, the statistics may end up preferring a sequential
scan.

-Doug
--
Free Dmitry Sklyarov!
http://www.freesklyarov.org/

We will return to our regularly scheduled signature shortly.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Denis Gasparin

denis@edinet.it

over 24 years ago

In reply to: Doug McNaught (#5)

Re:

I have done VACUUM ANALYZE too but the statistics continue preferring
sequential scan...

Now i'll try to use a different approach:
- i'll create the empty table with a CREATE TABLE (and a primary key on col1)
- then i'll populate it using then INSERT..SELECT statement
- Last i'll check what the statistics say about the SELECT on the primary
key query.

When i've done, i'll tell you...

Denis

At 19.03 23/08/01, Doug McNaught wrote:

Show quoted text

Denis Gasparin <denis@edinet.it> writes:

Hi to all!
I have created a table using the CREATE TABLE new_table
(col1,col2,col3) AS SELECT col1,col2,col3 FROM org_table.

I create an index on this table using the statement:
CREATE UNIQUE INDEX table_idx ON new_table (col1).
Then i do a select as this:
SELECT * FROM new_table WHERE col1 = 'value'.

The problem is that when i do an explain this is the query plan:

Seq Scan on new_table (cost=0.00..1116.38 rows=500 width=44)>

Can anyone explain me why it doesn't use the index I have created?

How populated is the table? If it's small, or if you haven't done
VACUUM ANALYZE, the statistics may end up preferring a sequential
scan.

-Doug
--
Free Dmitry Sklyarov!
http://www.freesklyarov.org/

We will return to our regularly scheduled signature shortly.

Denis Gasparin

denis@edinet.it

over 24 years ago

In reply to: Denis Gasparin (#7)

Re:

Now i have tried creating the table and the inserting...
The results are the same...
Is it possible that the query planner thinks that is best a sequential scan
when an index on the table is present?
I'm using postgresql 7.1.3 on a redhat 7.1.

Thanks for the help,
Denis

P.S.: I'm sorry having missed the subject of the mail....

At 11.54 24/08/01, Denis Gasparin wrote:

Show quoted text

I have done VACUUM ANALYZE too but the statistics continue preferring
sequential scan...

Now i'll try to use a different approach:
- i'll create the empty table with a CREATE TABLE (and a primary key on col1)
- then i'll populate it using then INSERT..SELECT statement
- Last i'll check what the statistics say about the SELECT on the primary
key query.

When i've done, i'll tell you...

Denis

At 19.03 23/08/01, Doug McNaught wrote:

Denis Gasparin <denis@edinet.it> writes:

Hi to all!
I have created a table using the CREATE TABLE new_table
(col1,col2,col3) AS SELECT col1,col2,col3 FROM org_table.

I create an index on this table using the statement:
CREATE UNIQUE INDEX table_idx ON new_table (col1).
Then i do a select as this:
SELECT * FROM new_table WHERE col1 = 'value'.

The problem is that when i do an explain this is the query plan:

Seq Scan on new_table (cost=0.00..1116.38 rows=500 width=44)>

Can anyone explain me why it doesn't use the index I have created?

How populated is the table? If it's small, or if you haven't done
VACUUM ANALYZE, the statistics may end up preferring a sequential
scan.

-Doug
--
Free Dmitry Sklyarov!
http://www.freesklyarov.org/

We will return to our regularly scheduled signature shortly.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Tom Lane

tgl@sss.pgh.pa.us

over 24 years ago

In reply to: Denis Gasparin (#8)

Re:

Denis Gasparin <denis@edinet.it> writes:

Is it possible that the query planner thinks that is best a sequential scan
when an index on the table is present?

Possibly. It all depends on the statistics. You have not shown us the
EXPLAIN results obtained after doing VACUUM ANALYZE...

regards, tom lane