Hi all,
I have two databases set up, one for development and one for
production. They are almost identical. I noticed that a query was
taking about 6.7 seconds to execute on the development database, but
only .08 seconds on the production database. The only difference was
there was another key for the production server (I am guessing it was an
index). So I clicked on the "Index" link in PhpPGAdmin for the id of
the table on the development database. This created another key just
like the one I had on the production. Now the query executed at .08
seconds. To further see what was going on, I removed the new index from
the dev DB and testing the query again... amazingly, too me, it was
still fast. Can anyone explain this to me? Thanks, Sean. Below is my
query:
SELECT
seasonal.id AS seasonal_id,
seasonal.title AS seasonal_title,
prod.id,
prod.title,
prod.co_title,
prod.summary,
prod.pic1,
prod.new_pic,
prod.new_date
FROM
seasonal,
xref_seasonal_p rod,
prod,
prod_opt
WHERE
seasonal.home = 't'
AND xref_seasonal_p rod.xref_season al_id = seasonal.id
AND xref_seasonal_p rod.xref_prod_i d = prod.id
AND prod.live = 't'
AND (prod_opt.clear an = 'f' OR (COALESCE(prod_ opt.quantity,0) -
COALESCE(prod_o pt.committed,0) ) > 0)
AND prod_opt.xref_p rod_id = prod.id
AND prod_opt.live = 't'
AND EXISTS
(
SELECT
a.id
FROM
prod_opt AS a
WHERE
a.xref_prod_id = prod.id
AND a.live = 't'
ORDER BY
a.priority
LIMIT 1
)
AND prod.mrf_only = 'f'
ORDER BY
xref_seasonal_p rod.priority,
prod.title
LIMIT 3
---------------------------------
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard 1 1485
On Fri, 7 Nov 2003 14:02:55 -0800 (PST), Sean <dc*****@yahoo. com>
wrote: To further see what was going on, I removed the new index from the dev DB and testing the query again... amazingly, too me, it was still fast. Can anyone explain this to me?
CREATE INDEX updates reltuples and relpages in pg_class. This might
cause the planner to choose a different plan. May I guess that you
didn't VACUUM or ANALYSE your table for a long time?
Servus
Manfred
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: 7 |
last post by:
I installed postgres and phppgadmin debian unstable
(on ProMepis RC4) but I can't seem to access it.
I type http://localhost/phppgadmin
and it says file not found.
Look in /var/www and sure enough, its not there and neither
are any files relating to the phppgadmin.
How do I get this thing to work?
|
by: Sean Gonsman |
last post by:
Hi all,
I have two databases set up, one for development and one for
production. They are almost identical. I noticed that a query was
taking about 6.7 seconds to execute on the development database, but
only .08 seconds on the production database. The only difference was
there was another key for the production server (I am guessing it was an
index). So I clicked on the "Index" link in PhpPGAdmin for the id of
the table on the...
|
by: CSN |
last post by:
In phppgadmin, primary keys are added/shown under
"constraints". Does creating a primary key
automatically create an index? No indexes are listed
under phppgadmin's indexes page, but "\d table" in
psql shows indexes for primary keys.
__________________________________
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/
|
by: eClaire |
last post by:
Hello,
I'm new in this list. First, scuse me for my not very good english and
please speak me in simple words ;)
I had install postgresql on my debian, no problem. I have too phppgadmin
and it is accessible in my browser locally.
But... if I want access the phppgadmin interface of another computer, I
have a "403 Forbidden" error. Do you know what is the solution?
|
by: Daniel Secomb |
last post by:
Hi,
I'm using PostgreSQL 7.3.4 with phpPgAdmin 2.4.2 and the ".sql" file I'm
trying to import came from a dump from a server running PostgreSQL 7.0.3.
I just got this error message trying to import the PostgreSQL database file
into phpPgAdmin. The error message was as follows:
Error -
/usr/local/psa/admin/htdocs/domains/databases/phpPgAdmin/db_readdump.php --
| |
by: Eric |
last post by:
Hi,
My webserver work in PHP4, safemode and it's running for years.
I use Debian, kernel 2.6 since yesterday.
I install psotgreSQL and PHPPGADMIN from apt-get (in .deb package) like
always on Debian...
I configure pg_hba.conf with
|
by: Chris Green |
last post by:
I'm not sure if this is really on topic for this list but I'll start
here.
I'm trying to run phpPgAdmin, I've installed version 3.5, I have
postgresql version 7.4.3 running on slackware 9.1.
I already have phpMyAdmin running successfully.
When I try and run phpPgAdmin I get:-
|
by: John Oliver |
last post by:
I have a Fedora Core 2 box with MySQL and phpMyAdmin running just fine.
I want to add PostGreSQL and phpPgAdmin I installed:
php-pgsql-4.3.10-2.4.i386.rpm
postgresql-7.4.7-3.FC2.1.i386.rpm
postgresql-libs-7.4.7-3.FC2.1.i386.rpm
postgresql-server-7.4.7-3.FC2.1.i386.rpm
I then added the extension=pgsql.so line to php.ini
|
by: Dan99 |
last post by:
I am not sure if I should post this here or in a potgre group, but I
will start here. I recently got a new server and thus when I copied
all the files to the new machine, I logically decided to upgrade
everything to the newest versions. So my postgre got upgraded from
7.4 to 8.1 and my phpPgAdmin also got upgraded. Now though, I am only
able to use the edit/delete buttons on some of the tables when
browsing. I am not sure what is the...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| | |