Обсуждение: Views in PgAccess
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
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
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
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
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.
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: >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
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.
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: >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
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