Postgres Pro
Downloads
Home   >   mailing lists

Thread: Views in PgAccess

Views in PgAccess

From
Fernando Schapachnik
Date:
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

From
Denis Gasparin
Date:
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

Re: Views in PgAccess

From
Fernando Schapachnik
Date:
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

Re: Views in PgAccess

From
Tom Lane
Date:
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

Re:

From
Doug McNaught
Date:
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.

Re:

From
Denis Gasparin
Date:
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

Re:

From
Denis Gasparin
Date:
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.

Re:

From
Denis Gasparin
Date:
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

Re:

From
Tom Lane
Date:
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