Re: [GENERAL] Database Select Slow

2007-08-10 Thread .ep 
On Aug 10, 9:42 pm, [EMAIL PROTECTED] ("A.
Kretschmer") wrote:
> am  Fri, dem 10.08.2007, um 17:46:11 +0800 mailte carter ck folgendes:
>
> > Hi all,
>
> > I am facing a performance issue here. Whenever I do a count(*) on a table
> > that contains about 300K records, it takes few minutes to complete. Whereas
> > my other application which is counting > 500K records just take less than
> > 10 seconds to complete.
>
> > I have indexed all the essential columns and still it does not improve the
> > speed.
>
> Indexes don't help in this case, a 'select count(*)' forces a seq. scan.
> Do you realy need this information? An estimate for the number of rows
> can you find in the system catalog (reltuples in pg_class, 
> seehttp://www.postgresql.org/docs/current/interactive/catalog-pg-class.html)



Hi, what if I need to do a count with a WHERE condition? E.g.,

SELECT count(*) from customers where cust_id = 'georgebush' and
created_on > current_date - interval '1 week' ;

Can I get the info about this from somewhere in the pg system tables
as well? Queries like these are very common in most applications, so
I'm hoping I can avoid the sequential scans!

Many thanks for any tips.


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

[GENERAL] LIKE conditions in PGSQL very, very slow!

2007-08-10 Thread .ep 
Hi,

I'm moving from the mysql camp and quite liking things like functions
and such, but a lot of my functionality depends on queries such as

   SELECT id, name, start_date
   FROM customer
   WHERE name LIKE 'eri%';

These kinds of queries are super fast in MySQL because "eri%" type
conditions also use the index. Is this not the case with PG?

Here's the EXPLAIN output:


CUSTDB=# explain select id,name,start_date from customer where name
like 'eri%';
   QUERY PLAN

 Seq Scan on customer  (cost=0.00..86032.18 rows=1 width=111)
   Filter: ((name)::text ~~ 'eri%'::text)
(2 rows)


Would appreciate any thoughts on how to make these kinds of queries
faster. I found a message (http://archives.postgresql.org/pgsql-sql/
1999-12/msg00218.php) but that's from 1999.

While we're at it, are compound indexes ok in PGSQL as well? In MySQL,
the order of columns is important if it reflects my WHERE conditions
in SQL. Should I follow the same structure in PGSQL? I tried looking
at the manual but did not find a section that talks about indexing in
detail. Would appreciate pointers.

Thanks!


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] Importing *huge* mysql database into pgsql

2007-03-06 Thread .ep 
Hello,

I would like to convert a mysql database with 5 million records and
growing, to a pgsql database.

All the stuff I have come across on the net has things like
"mysqldump" and "psql -f", which sounds like I will be sitting forever
getting this to work.

Is there anything else?

Thanks.


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/